Number of or percent of year Passed

J

Jim

Hi,

I am trying to find a formula that will return the percent of the year
passes. For example, I manually calculated that on today, 10/25/2008, 81%
of the year has passes. I need a formula that would self update.

Thanks, Jim
 
D

Dave Peterson

One way:

=(TODAY()-DATE(YEAR(TODAY()),1,0))/(365+(MONTH(DATE(YEAR(TODAY()),2,29))=2))

=date(year(today()),1,0)
returns Dec 31 of the previous year.

So the numerator is the number of days since that new year's eve.

The denominator checks to see if Feb 29 is in February.

Excel is pretty smart. It'll treat =date(2009,2,29) as March first. So the
denominator is just checking to see if the year is a leap year or not (use 366
or 365 as the number of days).
 
S

ShaneDevenshire

Hi,

Here is the rather long, but standard type of solution:

=(TODAY()+1-DATE(YEAR(TODAY()),1,1))/(DATE(YEAR(TODAY()),12,31)+1-DATE(YEAR(TODAY()),1,1))

And here is the short, but non-standard solution:

=YEARFRAC("1/1/2008",TODAY()+1,1)

You might make a case for removing the +1's from both formulas, but its
really how you want to treat it.

If this helps, please click the Yes button.
 
Top