Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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)) |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Elapsed Time | Excel Worksheet Functions | |||
Calculating elapsed time | Excel Discussion (Misc queries) | |||
Calculating Time elapsed | Excel Worksheet Functions | |||
Calculating elapsed time | Excel Worksheet Functions | |||
Calculating a rate for elapsed time? | Excel Discussion (Misc queries) |