Today is Monday 15 December 2003, so I put this date in say cell A1 and in cell B1 enter the function =DAY(A1). If B1 is formatted using the custom format d or dd I get 15 as expected, but if I format the cell using ddd or dddd I get Sun or Sunday and not Mon or Monday as I would expect. All the other date cells are one day behind.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel and =DAY( ) 4
- Thread starter kevinUK
- Start date
- Thread starter
- #3
![[lightsaber]](/data/assets/smilies/lightsaber.gif "[lightsaber] [lightsaber]")
![[shocked]](/data/assets/smilies/shocked.gif "[shocked] [shocked]")
-
4
- #5
You're formatting B1, the cell that contains the formula =DAY(A1). It says Sunday because January 15, 1900 was a Sunday (at least according to Excel, see below). Try formatting both A1 and B1 with various date formats and you will see what I mean.
In other words, =DAY(A1) produces the number 15, which when interpreted by Excel as a date yields January 15, 1900.
Interestingly, January 15, 1900 was really a Monday, too. But Excel reproduced the Lotus 1-2-3 bug which erroneously treated 1900 as a leap year (probably for compatibility). Consequently dates prior to March 1, 1900 are off by one in Excel.
Historical note: When I wrote a spool file to .csv application for an IBM AS/400 several years ago, I had to reproduce the same bug for the same reason (compatiblity).
In other words, =DAY(A1) produces the number 15, which when interpreted by Excel as a date yields January 15, 1900.
Interestingly, January 15, 1900 was really a Monday, too. But Excel reproduced the Lotus 1-2-3 bug which erroneously treated 1900 as a leap year (probably for compatibility). Consequently dates prior to March 1, 1900 are off by one in Excel.
Historical note: When I wrote a spool file to .csv application for an IBM AS/400 several years ago, I had to reproduce the same bug for the same reason (compatiblity).
Dunno 'bout anyone else but that's a very nice, explanation from Mr Z and I reckon it's worth a star
Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
Yes, that is a nice explanation.
I used to work with database owners that used an International Day Number for dates, and that number was always 1 day different from the Excel date number. So it looks like the leap year problem was the reason.
So thanks for the explanation Zathras, and also have a star from me.
Cheers, Glenn.
I used to work with database owners that used an International Day Number for dates, and that number was always 1 day different from the Excel date number. So it looks like the leap year problem was the reason.
So thanks for the explanation Zathras, and also have a star from me.
Cheers, Glenn.
TonyJollans
Programmer
As an addendum to Zathras' fine post, it's worth noting that VBA does not reproduce the same bug, so 15 formatted as a date in VBA returns Sunday 14 January 1900.
Enjoy,
Tony
Enjoy,
Tony
mscallisto
Technical User
Anything that saves a headache for someone else is worth a star.
And yes I do remember that @^%$$# leapyear problem from years back.
And yes I do remember that @^%$$# leapyear problem from years back.
- Status