CODEWITHSUNDEEP
vbaexceldate
Pierre44
Pierre44

Reputation: 1741

VBA doesn't return the correct Date with Now()

I have a simple code and VBA cannot return the correct date: 

Sub Test_Date ()

Debug.Print Format(Month(Now()), "mmm")
Debug.Print Month(Now())
Debug.Print Now()

End Sub

Returns: 

Dez
 1 
29.01.2018 11:17:55

1) Is that an Excel well known issue or did I make a mistake?

Upvotes: 2

Views: 1565

Answers (1)

Vityata
Vityata

Reputation: 43585

It is a known issue. And it is actually not an issue.

In Debug.Print Format(Month(Now()), "mmm") you are passing Debug.Print Format(1,"mmm").

And the first day in VBA is 31.12.1899. And its month is December. Run this small piece of code to see it:

Sub TestMe()
    Debug.Print Format(1, "dd-mmm-yyyy")
End Sub

If you want the month of the current date - Format(Now, "mmm")

In general, be a bit careful, when you are working with dates in Excel, VBA and MSSQL Server. The dates are converted to numbers. E.g., every date is converted to a number, but the starting number can be a bit different.

  • In Excel, the 1 is converted to 01.January.1900;
  • In VBA, the 1 is converted to 31.December.1899;
  • In MSSQL Server, the 1 is converted to 02.January.1900 (SELECT CONVERT(DATETIME,1));
  • In Excel, if you activate the ActiveWorkbook.Date1904=True property, the 1 is converted to 02.January.1904;

The reason for this is an error, made by the creators of Lotus 1-2-3, who have thought that 29.02.1900 was a valid date. Thus, Excel wanted to become compatible with Lotus 1-2-3 and they have carried on with this error. In VBA, they have decided not to implement the error, thus the dates in VBA and Excel are a bit different, but this is only for the period up to 1. March 1900 - MSDN date explanation.

Thus, depending on which one of the 4 "environments" above you are working, today's date (29-January-2018) can be converted to one of the following:

  • 41667 (Excel with Date1904)
  • 43128 (MSSQL Server SELECT CONVERT(INT, CONVERT(DATETIME,GETDATE())))
  • 43129 (Excel)
  • 43129 (VBA)

If you take the 35. day and convert it to date in Excel, VBA and MSSQL Server, the result will be as follows:

  • 03.February.1900 (VBA)
  • 04.February.1900 (Excel)
  • 05.February.1900 (MSSQL Server - SELECT CONVERT(DATETIME,35))
  • 05.February.1904 (Excel with Date1904)

This article (written by the owner of SO) gives some addition enlightment: My First BillG Review

Upvotes: 9

Related Questions