Dates & Times
Date Data Type
You should always try and store your dates in variables that have the Long datatype
The calendar exposes the calendar system that is currently being used.
There are two types of calendar. The default is vbCalGreg. The other is vbCalHijri.
The Date property expects a date passed to it
Any time information is ignored
The Date$ property returns and assigns dates from strings
minimum date - 1 jan 1980
maximum date - 31 dec 2099
Always declare dates as Long (never Date).
If a subroutine requires a Date datatype, pass in a Long instead and it will be converted automatically.
In VBA dates and times are enclosed between two hash (#) signs, for example:
Dim dtExpiries As Date
dtExpiries = #12/31/2006# 'this is interpreted as m/d/yyyy by default - check !!!
dtExpiries = #31/12/2006#
dtExpiries = "December 31, 2006"
dtExpiries = #01:30:00 AM#
dtExpiries = #31/12/06 07:30:00 PM#
VBA will automatically adjust the date if it is entered in the "dd/mm/yyyy" format to "mm/dd/yyyy".
dtExpiries = #31/12/2006# 'automatically changed from #12/31/2006#
VBA will also automatically adjust the time into AM/PM notation if it is entered in 24hr notation.
dtExpiries = #07:30:00 PM# 'autoamtically changed from #19:30:00#
Date variables are displayed accordingly to your systems shortdate format.
Times are displayed according to your systems time format, either 12 or 24 hour format.
These settings can be changed from your control panel.
For more details, refer to the Dates & Times page.
Formatting Dates in Excel
It is recommended you use date formats that begin with an asterisk (*) in the Format Cells dialog box.
Any date formatted in this way will display correctly in a different region.
Switching this to "English (UK)".
Notice that none of the available formats have an asterisk
Therefore this will be the date format in every region regardless of regional settings.
If you want to see different date formats for different regions, you should access the "Region and Language" dialog box from the control panel.
Customise your default date format
If you have your setting set to "English (UK)" then the default date format is "dd/mm/yyyy"
If you would rather not see the extra zeros (eg 1/7/2010 instead of 01/07/2010) this can be changes from the control panel.
Display a time using the short time format specified in your computer's regional settings.
To get todays date in a readable format you can use the Format() function, eg Format("Now(), "dd MMM YY") = "01 Jul 03".
This Pauses for 5 seconds
Nexttime = Now() + TimeValue("00:00:05")
You can use a string variable to store your dates but you obviously cannot perform any date or time calculations on it.
Dates - Using and Formatting
'when the date is 26/02/2009
spastedate = Format(DateAdd("d", 3, Date()), "dd/mm/yyyy")
spastedate = "03/01/2009"
spastedate = FormatDateTime(DateAdd("d", 2, Date()), VbDateTimeFormat.vbShortDate)
spastedate = "03/01/2009"
Range("A1").Value = spastedate
'depending on how the cell is formatted - can depend on how the results are displayed.
If the cell is formatted as a date then you can get "03/01/2009"
If the cell is formatted as text then you get "01/03/2009"
'This will display the correct date value
Range("A1").Value = dtpastedate
If you do have to have strings of dates - then make sure they are converted to dates before you paste them on to the sheet
|General||Uses the system Short Date format|
|Date (default)||Dates that contain times are dsplayed in Long Time format. Dates outside 1930-2029 will always be formatted with four-digits for there year component, regardless of the Short Date format|
|Long Date||Uses the System Long Date format|
|Medium Date||Uses a format applicable to the current system locale|
|Short Date||Uses the system Short Date format|
|Long Time||Uses the system Time format|
|Medium Time||Uses the 12-hour format|
|Short Time||Uses the 24-hour format|