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

Try this website

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=559593

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

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

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" wrote in message
news:daddylonglegs.2amwmn_1152375002.7104@excelfor um-nospam.com...
|
| 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
|