Excel 2007 - calculating % of a year that has elapsed


J

john

How do I calculate what percentage of a year has elapsed?
I have the date 31/12/09 in cell A1 and the date 31/05/09 in cell B1 and
then I do this: =B1/A1*100 but this formula is wrong.How should I do the
calculation?
Thanks, John
 
Ad

Advertisements

N

Niek Otten

=1+(B1-A1)/365

If you want to account for leap years, use a third cell with 31-12-2008. If
this is in A2, use this formula:

=1+(B1-A1)/(A1-A2)
 
P

Pat Garard

G'Day John,

A 'date' in Excel is equal to the number of days since January 1, 1900 at
12:00am.
(The fractional part is the time of day - if you do not enter a time the
fractional part = .00000... = 12:00am.)

You need in:
A1 B1 C1
31/12/2008 31/12/2009 31/5/2009

B1-A1 = number of days in 2009 (need this for leap years)
C1-A1 = number of this day in 2009
%Year = 100*(C1-A1)/(B1-A1) OR
%Year = (C1-A1)/(B1-A1) formatted as %
 
J

JoeU2004

john said:
How do I calculate what percentage of a year has elapsed?
I have the date 31/12/09 in cell A1 and the date 31/05/09 in cell B1 and
then I do this: =B1/A1*100 but this formula is wrong.How should I do the
calculation?
One way:

=(B1 - date(year(B1)-1,12,31)) / (A1 - date(year(B1)-1,12,31))

FYI, you do not need A1. You could do:

=(B1 - date(year(B1)-1,12,31)) / (date(year(B1),12,31) -
date(year(B1)-1,12,31))
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top