Understanding the Month Function in a VBA

I am writing to inform you of an event that seems to be a bug regarding the month function.

When I ran the following vba code without any description in the A1 cell on December 16, 2019, and A2 cell, it was displayed as a bug.

Subtest()
    If Month(Cells(1,1)) = Month(Cells(2,1)) Then
        MsgBox ("bug!")
    Else
        MsgBox ("normal!")
    End If
End Sub

In other words, month (cell A1) and month (cell A2) are determined to be equal to 12 (actually, when the value of Month (cells(2,1) is confirmed in debug.print), 12 is output.I didn't actually type anything in cells(2,1)!)

The incident has been identified in Microsoft Office Professional Plus 2016.

Please let me know if anyone knows about this specification.

vba

2022-09-30 21:44

1 Answers

Please let me know if anyone knows about this specification.

This is because cells that have not been entered in Excel are judged to be numeric 0, and specification interprets it as 1899/12/30.

I will write a detailed explanation below, if you are interested...

A long time ago, Joel Spolsky (who designed Excel ver5) wrote in a blog post, "Why is Microsoft Office file format so complicated?"I can't find the article from that time now, but
Now that some people are barely publishing a copy of the time, please make it reference.

In order to capture the data of the spreadsheet software (Lotus 1-2-3), which was famous at the time, the date calculation was incorporated into Excel knowing the bug.

There are some strange features of Excel date processing.

1) The date reference value can be changed to 1904.
　　
In the File menu, under Advanced Settings,
　　　There is a check box called "Calculate from 1904".

　　If you check this, enter 0 in the Excel cell and set the format to date format
　　　1904/1/1 appears.　　

2) To capture Lotus 1-2-3's 1900 leap year miscalculation bug as it is
　　The reference value of the date is 1899/12/30.
　 The standard value is December 30th, so it looks a little half-hearted.
　　Huh? If you type 1 in the Excel table and change the format to date format, 1900/1/1
That's right. Isn't it consistent with the specification whose reference value is 1899/12/30??

The int->date type conversion results are different between spreadsheets and VBA calculations.

Actually, the value will be the same after 1900/3/1 but
　　VBA calculates 1900 is not a leap year (because of Lotus 1-2-3 compatibility)
　　The next day after the bugged 1900/2/29 the initial value of the Date type is
based on the value of 1900/3/1 as 61. 　　Implemented with leap year calculation bugs.

Therefore, it is safer not to use the date from 1899/12/30 to 1900/3/1

It sounds like a chat, but there are a lot of people who don't know...

2022-09-30 21:44

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656