Excel 2007 - calculating % of a year that has elapsed

[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

 

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

 

[Niek Otten]

O yes, and format as %

 

[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 %

 

[JoeU2004]

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

 

[john]

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