Excel and =DAY( ) 4

[kevinUK]

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.

 

[ETID]

try =weekday(a1)

 

[kevinUK]

Many thanks, yes =weekday(a1) does work (with ddd format), but I was just interested to find out why =day(a1) did not give the correct answer. KevinUK

 

[NiteLink]

If I'm not wrong, weekday is supposed to be a format for the date itself.

Fight?

What fight?

 

[Zathras]

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).

 

[xlbo]

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

 

[GlennUK]

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.

 

[TonyJollans]

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

 

[mscallisto]

Anything that saves a headache for someone else is worth a star.

And yes I do remember that @^%$$# leapyear problem from years back.