Hi,
I have an Excel/VBA UserForm that accepts the date that a record is created (manual entry ito a textbox or via click of a command button which fills the textbox with the system date from the Date() function.) I've used a temporary mgsbox, formatting the output to confirm that the correct date is being set:
returns 07/Jan/2014 as expected.
When the user clicks another command button to save the new record I use the following:
The msgbox still returns the date in the correct format: "07/Jan/2014".
However, the actual date being stored in the Excel spreadsheet reverses the month and date numbers somehow, so the actual date value stored is 41821, or July 1st, 2014. The cell is formatted with the Short Date format and shows as "01/07/14".
Can anyone explain why this is, and most usefully, tell me what I can change so that the correct date (Jan 7th 2014 in this example) is stored?
Many thanks
Steve
I have an Excel/VBA UserForm that accepts the date that a record is created (manual entry ito a textbox or via click of a command button which fills the textbox with the system date from the Date() function.) I've used a temporary mgsbox, formatting the output to confirm that the correct date is being set:
Code:
Me!DateBookingMade = Format(Date, "dd/mm/yyyy")
MsgBox (Format(Me!DateBookingMade, "dd/mmm/yyyy"))
When the user clicks another command button to save the new record I use the following:
Code:
ActiveCell.Offset(0, 17) = Format(Me!DateBookingMade, "dd/mm/yyyy") MsgBox (Format(Me!DateBookingMade, "dd/mmm/yyyy"))
However, the actual date being stored in the Excel spreadsheet reverses the month and date numbers somehow, so the actual date value stored is 41821, or July 1st, 2014. The cell is formatted with the Short Date format and shows as "01/07/14".
Can anyone explain why this is, and most usefully, tell me what I can change so that the correct date (Jan 7th 2014 in this example) is stored?
Many thanks
Steve