Forum Discussion
day of week
I am trying to display day of week using a date prior to 1900 any ideas?
- This formula only works in the inserted file ( custom-made)...
...is VBA code :).
Hope I was able to help :).
I know I don't know anything (Socrates)
4 Replies
- This formula only works in the inserted file ( custom-made)...
...is VBA code :).
Hope I was able to help :).
I know I don't know anything (Socrates)
- Well Done just what I was looking for
Excel cannot handle dates before 1900 (and also, the weekday of dates before March 1, 1900 is not correct). If you enter for example 01-Jan-1878 in a cell, it will be left-aligned, because Excel does not recognize it as a date, and treats it as text.
As a workaround, you can enter the year, month and day in separate cells.
With year in A2, month in B2 and day in C2, the weekday number (1=Sunday, 2=Monday etc.) is returned by the formula
=WEEKDAY(DATE(A2+IF(OR(A2<1900,AND(A2=1900,B2<3)),400,0),B2,C2))
If you want to see the name of the day of the week:
=TEXT(DATE(A2+IF(OR(A2<1900,AND(A2=1900,B2<3)),400,0),B2,C2),"dddd")
Warning: this will only work for dates using the Gregorian calendar - see Adoption by country for when this calendar was adopted.
Excel formulae don't work with dates before 1900, that's with VBA or Power Query.
