Problem using WorksheetFunction.EoMonth with 1904 date system in VBA

[Sunjinsak]

Hi folks,

Excel 2013 on Windows 10

I need to find the last day of the current month and the last day of the current month plus 12 months. So for example at the time of writing this post I’d need to calculate 31/01/2020 and 31/12/2020. This has to be done in VBA as it’s a smaller part of a much bigger algorithm.

Here is some example code for how I’ve been doing this:

Dim dateOne As Date, dateTwo As Date

dateOne = WorksheetFunction.EoMonth(Date, 0)
dateTwo = WorksheetFunction.EoMonth(Date, 11)

Debug.Print "Date ONE: " & dateOne
Debug.Print "Date TWO: " & dateTwo

I wrote and tested this in a separate Workbook and it worked as expected, correctly returning 31/01/2020 and 31/12/2020 respectively. However when I copied the code over to the actual Workbook it is destined for and re-tested it returned 30/01/2012 and 30/12/2012.

At first I was flummoxed but then I realised this Worksheet was using the 1904 date system. The reason for this is it has to be able to calculate and display negative values for time (e.g. -01:00) and so I cannot simply switch to using the standard 1900 date system. However, to confirm my theory I temporarily changed the date system and re-tested my code and it worked correctly again. So it is definitely the use of the 1904 date system that is throwing it off.

What I really want to know though, is why is it returning 2012 instead of 2016? According to Microsoft the difference between the two date systems, as you might expect, is 1462 days - that’s 4 years (including one leap day). However as you can see the difference here is twice that. What am I missing?

To overcome the above I can alter the code like so…

dateOne = WorksheetFunction.EoMonth(DateAdd("d", 2924, Date), 0)
dateTwo = WorksheetFunction.EoMonth(DateAdd("d", 2924, Date), 11)

It’s now returning the correct year, but the other problem the eagle-eyed amongst you may have spotted still remains – namely that it is returning the last day of January and December as 30 and not 31. Why is this?

I can easily test for the use of the 1904 date system with ActiveWorkbook.Date1904 = True/False then add 2924 days/8 years to Date() as necessary, but it seems a bit hacky and I don’t know if it will still work correctly next month, next year or in 5 years because I don’t know what’s causing the apparent discrepancy of an extra 4 years in the first place. Also, as noted above this doesn’t solve the problem with the last day of the month being incorrectly calculated.

So, is there a way to correctly use WorksheetFunction.EoMonth with the 1904 date system, and if not is there an alternative method for calculating the last day of any given month I can use (preferably with either date system)?

Thanks.

 

[CSmith]

Maybe this will help?

 

[RoryA]

You can simply avoid the Worksheetfunction method:

dateOne = DateSerial(Year(Date), Month(Date) + 1, 0)
dateTwo = DateSerial(Year(Date), Month(Date) + 12, 0)

 

[Sunjinsak]

@RoryA That works perfectly with both date systems and is exactly what I'm looking for. Thank you.

Out of curiosity does anyone know why there is a difference of 8 years instead of 4 years when using the 1904 date system with the code I posted? The link kindly posted by @CSmith sheds some light as to what might be causing the discrepancy between the last days of the month but not the 4/8 year issue. I know its a moot point now that @RoryA has posted a better solution, but still it would be interesting to find out.