Day of week for pre 1900 dates

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

[VBA Noob]

Try this website

http://j-walk.com/ss/excel/files/xdate.htm

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


"ddmac" wrote:

>
> 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.
>
>
> --
> ddmac
> ------------------------------------------------------------------------
> ddmac's Profile: http://www.excelforum.com/member.php...o&userid=36185
> View this thread: http://www.excelforum.com/showthread...hreadid=559593
>
>

[daddylonglegs]

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"

[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

"daddylonglegs" <[email protected]> wrote in message
news:[email protected]...
|
| 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...o&userid=30486
| View this thread: http://www.excelforum.com/showthread...hreadid=559593
|