Day of week for pre 1900 dates

D

ddmac

Does anyone have any ideas on how to derive the day of the week for a
list of pre-1900 dates, each of which is presented in a three cell
horizontal format YEAR MONTH DAY, ie 1685 March 21.
I have a spreadsheet with over 1300 lines in this format, so any
function or calculation that can be entered into a blank cell on each
line to get the actual day for that line is the preferred solution.
Thanks in advance.
 
M

Martin P

In cells A1, B1 and C1 enter year, month and day respectively.
In cell E1 enter
=DATE(A1+400,B1,C1)-DATE(A1+399,12,31)
In cell G1 enter
=INT((A1-1)/4)-INT((A1-1)/100)+INT((A1-1)/400)
In cell H1 enter
=365*(A1-1)+E1+G1
In cell I1 enter
=MOD(H1,7)+1
In cell J1 enter
=TEXT(I1,"dddd")
J1 should give the weekday for any date, not only for before a certain date.
The assumption here is that the calendar started on 1 January of the year 1
and that the rules for leap years were followed from then. Historically
there is more to it than this.
 
D

daddylonglegs

If you download John Walkenbach's XDATE functions as suggested abov
then with your year, month, day, e.g. 1685, March, 21 in A1, B1, C
then use this formula to get weekday in text format, e.g. Wednesday

=TEXT(XDATEDOW(XDATE(A1,MONTH("1 "&B1),C1)),"dddd")

note: assumes your using Excel's default 1900 date system. For th
example above it gives me "Wednesday
 
N

Niek Otten

And do read John's warning about limitations: you have to know what date system was in use where and when the date was
established; even in the 19th century there were several date differences between countries.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| If you download John Walkenbach's XDATE functions as suggested above
| then with your year, month, day, e.g. 1685, March, 21 in A1, B1, C1
| then use this formula to get weekday in text format, e.g. Wednesday
|
| =TEXT(XDATEDOW(XDATE(A1,MONTH("1 "&B1),C1)),"dddd")
|
| note: assumes your using Excel's default 1900 date system. For the
| example above it gives me "Wednesday"
|
|
| --
| daddylonglegs
| ------------------------------------------------------------------------
| daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30486
| View this thread: http://www.excelforum.com/showthread.php?threadid=559593
|
 
Top