+ Reply to Thread
Results 1 to 3 of 3

Display day of the year prior to 1900

  1. #1
    Registered User
    Join Date
    01-22-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Display day of the year prior to 1900

    I've been using the below formula to extract the day of the year numerically (between 1 and 366 [leap years included]) however cannot get the function to perform for dates prior to 1/1/1900. For example how can I get an output of "32" for the date 1/02/1889? Any ideas?

    Here's the formula that works for post 1900: "day of the year"=C1+1-DATE(YEAR(C1),1,1)

    I'm also trying to find a formula that will extract the week of the year for the same dataset. I'm currently using "week of the year"=(TRUNC(D1/7))+1 however this returns a 6 day week for the first week of each year. Any ideas would be great!

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Display day of the year prior to 1900

    Native Excel functions cannot work with dates prior to Jan 1, 1900
    A possible alternative is to use the XDATE add-in available here:
    http://j-walk.com/ss/excel/files/xdate.htm

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    01-22-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Display day of the year prior to 1900

    I've used the XDATE add-in's with no success. They have provided the month and year extractions but I can't get them to work for the daily or weekly

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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