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; (30693.75, « »)
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)
http://excel.itags.org/q_microsoft-excel_117535.html
All Comments
Leave a comment...
- 5 Comments
- On Wed, 20 Sep 2006 12:52:01 -0700, Jim J. <JimJ.excel.itags.org.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.itags.org.discussions.microsoft.com> wrote in message
news:D448E288-59A7-462A-819E-65B655062E5C.excel.itags.org.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.itags.org.discussions.microsoft.com> wrote in message
news:D448E288-59A7-462A-819E-65B655062E5C.excel.itags.org.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.itags.org.discussions.microsoft.com> wrote in message
> news:D448E288-59A7-462A-819E-65B655062E5C.excel.itags.org.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.itags.org.Shucks.demon.co.uk> wrote in message
news:u%23HkoFP3GHA.4588.excel.itags.org.TK2MSFTNGP04.phx.gbl...
> One way:
> =INT(A366-DATE(YEAR(A366)-1,12,31))
> Regards
> Trevor
>
> "Jim J." <JimJ.excel.itags.org.discussions.microsoft.com> wrote in message
> news:D448E288-59A7-462A-819E-65B655062E5C.excel.itags.org.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