day of week

[MarkBeck54]

I am trying to display day of week using a date prior to 1900 any ideas?

[#2]

@MarkBeck54 

Excel formulae don't work with dates before 1900, that's with VBA or Power Query.

[#3]

@MarkBeck54 

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.

[#4 · Accepted solution]

@MarkBeck54 

This formula only works in the inserted file ( custom-made)...

...is VBA code :).

Hope I was able to help :).

NikolinoDE

I know I don't know anything (Socrates)

[#5]

Well Done just what I was looking for