Dates & Times

Date Data Type

You should always try and store your dates in variables that have the Long data type
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 data type, pass in a Long instead and it will be converted automatically.

American Format

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.

VBA.vbDateTimeFormat Constants


VBA.vbDayOfWeek Constants



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.

Locale/Location drop-down

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")
Application.Wait Nexttime

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

GeneralUses 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 DateUses the System Long Date format
Medium DateUses a format applicable to the current system locale
Short DateUses the system Short Date format
Long TimeUses the system Time format
Medium TimeUses the 12-hour format
Short TimeUses the 24-hour format

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopNext