# Day of week for pre 1900 dates

1. ## Day of week for pre 1900 dates

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.

2. Try this website

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

3. ## RE: Day of week for pre 1900 dates

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

4. 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"

5. ## Re: Day of week for pre 1900 dates

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

|
| 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"
|
|
| --
| ------------------------------------------------------------------------
|

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1