User FAQs

If you have a question, please send it to us.

1) How can you create a Date ?
The DATESERIAL function returns a date given a year, month and day.

Dim dtDate As Date 
dtDate = DateSerial(2023,7,7) 

2) When should I use the Date data type ?
You should use the Date data type when you are working with Dates, Times or Dates AND Times.
Using the Date data type rather than the Long data type makes your code more readable and easier to understand.

Dim dtDate As Date 

3) Why do some people use the Long data type ?
If you are only concerned with dates (as opposed to dates with times) you can use the Long data type.
The reason some people use the Long data type is because it takes up slightly less memory.
The Long data type uses 4 bytes, whereas the Date data type uses 8 bytes.

Dim lLong As Long 

4) How can I insert a date into a cell ?
Enter the date serial number and then apply formatting.
Do not format the date before you enter it.

Dim rgeRange As Excel.Range 
Set rgeRange = Range("A1") 
rgeRange.Value = VBA.Date 
rgeValue.NumberFormat = "dd/mm/yyyy" 

5) How can I remove the time component from a Date and just get the whole number ?
Use the INT function to return the integer portion of a decimal number.

Dim dtDate As Date 
Dim lLong As Long 
lLong = INT(dtDate) 

Do not use CLNG or ROUND because both these functions can apply rounding.

6) How can you display a date in a specific format ?
Use the FORMAT function to return a date or date-string into a particular format.

Dim dtDate As Date 
MsgBox Format(dtDate, "dd MMM yyyy") 

7) Can I use a string literal to initialise the value ?
You can but be aware that these dates ignores the regional settings and are always interpreted in US date format (mm/dd/yyyy).

Dim dtDate As Date 
dtDate = #01/02/22#  '= 2 January 2022  

8) How would you convert a UK date string into a Date data type ?
Extract each of the components from the text string and the use the DATESERIAL function to

Dim sDateString As String 
sDateString = "01/02/2022 12:30 PM" 
Dim dtDate As Date 
dtDate = DateSerial(Mid(sDateString, 7, 4), 
                    Mid(sDateString, 4, 2), 
                    Left(sDateString,2)) 

9) What is the difference between the FORMAT function and the FORMATDATETIME function ?
FORMAT - Returns the expression formatted as a date or time in ANY custom format.
FORMATDATETIME - Returns the expression formatted as a date or time in few predefined formats.

Debug.Print Format(Now, "dd MMM yyyy") '= 24 Jan 2022  
Debug.Print FormatDateTime(Now, vbDateTimeFormat.vbGeneralDate) '= 24/01/2022 17:46:57  

10) What is the difference between the INT function and the FIX function ?
These 2 functions handle positive numbers the same but handle negative numbers slightly differently.
INT - Returns the integer portion of a number. The first negative integer less than or equal to the number.
FIX - Returns the integer portion of a number. The first negative integer greater than or equal to the number.

Debug.Print Int(-1.1) '= -2  
Debug.Print Fix(-1.1) '= -1  

11) What is the difference between the CDATE function and the CVDATE function ?
CDATE - Returns an actual Date data type.
CVDATE - Returns a Variant data type whose sub type is Date. Only provided for backwards compatibility.

Debug.Print TypeName(CDate(dtDate, "dd MMM yyyy")  '= Date  
Debug.Print TypeName(CVDate(dtDate, "dd MMM yyyy") '= Variant/Date