+ Reply to Thread
Results 1 to 5 of 5

Day of week for pre 1900 dates

  1. #1
    Registered User
    Join Date
    07-08-2006
    Posts
    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.
    Thanks in advance.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try this website

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

  3. #3
    Martin P
    Guest

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


  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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. #5
    Niek Otten
    Guest

    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

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



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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