Tags: acirc, based, days360acirc, determine, excel, microsoft, msdn, number, software

Day Number For 365 Day Year

On Microsoft » Microsoft Excel

6,100 words with 5 Comments; publish: Fri, 23 May 2008 18:39:00 GMT; (306640.63, « »)

How can one determine the correct day of the year, based on a 365 day year?

I can use â'DAYS360â' to find the number of todayâ's date

=DAYS360(1/1/2006,9/20/2006,FALSE) will yield â'259â'. But the actual day

number based on a 365 day year is 263. I don't care about a 360 days year (I

don't live there!). Again, how can I calculate the actual day number based

on a 365 day year?

PS Why does this formula yield â'38420â' instead of â'259â?:

=DAYS360(1/1/2006,TODAY(),FALSE)

All Comments

Leave a comment...

  • 5 Comments
    • On Wed, 20 Sep 2006 12:52:01 -0700, Jim J. <JimJ.excel.questionfor.info.discussions.microsoft.com>

      wrote:

      >How can one determine the correct day of the year, based on a 365 day year?

      >I can use ?DAYS360? to find the number of today?s date

      >=DAYS360(1/1/2006,9/20/2006,FALSE) will yield ?259?. But the actual day

      >number based on a 365 day year is 263. I don't care about a 360 days year (I

      >don't live there!). Again, how can I calculate the actual day number based

      >on a 365 day year?

      A1: 9/20/2006

      A2: =A1 - DATE(YEAR(A1),1,0)

      Format as General or Number

      >PS Why does this formula yield ?38420? instead of ?259':

      >=DAYS360(1/1/2006,TODAY(),FALSE)

      Because your start date is a very small number:

      1/1/2006 = 1 divided by 2006 = 0.000498504

      Your Days360 function reduces to:

      =DAYS360(0,TODAY(),FALSE) which is the number of days since 12/31/1899 or

      38420.

      If you want the string to be interpreted as a date, inside a function, you can

      use:

      =DAYS360("1/1/2006",TODAY(),FALSE)

      or

      =DAYS360(DATE(2006,1,1),TODAY(),FALSE)

      --ron

      #1; Fri, 23 May 2008 18:40:00 GMT
    • One way:

      =INT(A366-DATE(YEAR(A366)-1,12,31))

      Regards

      Trevor

      "Jim J." <JimJ.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:D448E288-59A7-462A-819E-65B655062E5C.excel.questionfor.info.microsoft.com...

      > How can one determine the correct day of the year, based on a 365 day

      > year?

      > I can use 'DAYS360' to find the number of today's date

      > =DAYS360(1/1/2006,9/20/2006,FALSE) will yield '259'. But the actual day

      > number based on a 365 day year is 263. I don't care about a 360 days year

      > (I

      > don't live there!). Again, how can I calculate the actual day number

      > based

      > on a 365 day year?

      > PS Why does this formula yield '38420' instead of '259'?:

      > =DAYS360(1/1/2006,TODAY(),FALSE)

      #2; Fri, 23 May 2008 18:41:00 GMT
    • > PS Why does this formula yield '38420' instead of '259'?:

      > =DAYS360(1/1/2006,TODAY(),FALSE)

      1/1/2006 is the equivalent of 1 divided by 1 divided by 2006

      Try it this way:

      =DAYS360("1/1/2006",TODAY(),FALSE)

      Based on a 365(6) day year:

      =TODAY()-DATE(YEAR(TODAY()),1,1)+1

      Format as GENERAL

      Biff

      "Jim J." <JimJ.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:D448E288-59A7-462A-819E-65B655062E5C.excel.questionfor.info.microsoft.com...

      > How can one determine the correct day of the year, based on a 365 day

      > year?

      > I can use 'DAYS360' to find the number of today's date

      > =DAYS360(1/1/2006,9/20/2006,FALSE) will yield '259'. But the actual day

      > number based on a 365 day year is 263. I don't care about a 360 days year

      > (I

      > don't live there!). Again, how can I calculate the actual day number

      > based

      > on a 365 day year?

      > PS Why does this formula yield '38420' instead of '259'?:

      > =DAYS360(1/1/2006,TODAY(),FALSE)

      #3; Fri, 23 May 2008 18:42:00 GMT
    • Trevor,

      I do not understand the A366 portion of the equation.

      Thanks,

      Jim J.

      "Trevor Shuttleworth" wrote:

      > One way:

      > =INT(A366-DATE(YEAR(A366)-1,12,31))

      > Regards

      > Trevor

      >

      > "Jim J." <JimJ.excel.questionfor.info.discussions.microsoft.com> wrote in message

      > news:D448E288-59A7-462A-819E-65B655062E5C.excel.questionfor.info.microsoft.com...

      > > How can one determine the correct day of the year, based on a 365 day

      > > year?

      > > I can use 'DAYS360' to find the number of today's date

      > > =DAYS360(1/1/2006,9/20/2006,FALSE) will yield '259'. But the actual day

      > > number based on a 365 day year is 263. I don't care about a 360 days year

      > > (I

      > > don't live there!). Again, how can I calculate the actual day number

      > > based

      > > on a 365 day year?

      > > PS Why does this formula yield '38420' instead of '259'?:

      > > =DAYS360(1/1/2006,TODAY(),FALSE)

      >

      >

      #4; Fri, 23 May 2008 18:43:00 GMT
    • That's just the cell with the date in it ... maybe an unfortunate choice

      ;-)

      Could just as easily be:

      =INT(A2-DATE(YEAR(A2)-1,12,31)) where A2 has the date in it

      Alternatively:

      =INT(A2-DATE(YEAR(A2),1,0))

      day 0 of month 1 this year = day 31 of month 12 last year

      Regards

      Trevor

      "Trevor Shuttleworth" <Trevor.excel.questionfor.info.Shucks.demon.co.uk> wrote in message

      news:u%23HkoFP3GHA.4588.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

      > One way:

      > =INT(A366-DATE(YEAR(A366)-1,12,31))

      > Regards

      > Trevor

      >

      > "Jim J." <JimJ.excel.questionfor.info.discussions.microsoft.com> wrote in message

      > news:D448E288-59A7-462A-819E-65B655062E5C.excel.questionfor.info.microsoft.com...

      >> How can one determine the correct day of the year, based on a 365 day

      >> year?

      >> I can use 'DAYS360' to find the number of today's date

      >> =DAYS360(1/1/2006,9/20/2006,FALSE) will yield '259'. But the actual day

      >> number based on a 365 day year is 263. I don't care about a 360 days

      >> year (I

      >> don't live there!). Again, how can I calculate the actual day number

      >> based

      >> on a 365 day year?

      >> PS Why does this formula yield '38420' instead of '259'?:

      >> =DAYS360(1/1/2006,TODAY(),FALSE)

      >

      #5; Fri, 23 May 2008 18:44:00 GMT