Excel 2007 - calculating % of a year that has elapsed
 

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

Excel 2007 - calculating % of a year that has elapsed
 

=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)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"john" wrote in message
...
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

Excel 2007 - calculating % of a year that has elapsed
 

O yes, and format as %

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"john" wrote in message
...
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

Excel 2007 - calculating % of a year that has elapsed
 

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 %
--
Regards,
Pat Garard
Melbourne, Australia
_______________________



"john" wrote:

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

Excel 2007 - calculating % of a year that has elapsed
 

"john" wrote:
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))

Excel 2007 - calculating % of a year that has elapsed
 

Thanks everyone for your assistance.(Now I can move on.)
John

"john" wrote:

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