Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating dates
=(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12)
is this the easiest way to calculate number of years(including fractions) between two dates? I thought =YEAR(B7)-YEAR($B$6) would have done it if b7 = 3/12/1999 and b6 = 1/12/1998 the answer is 1.00 so it's apparently just rounding the number to whole years the first formula seems to work but I didn't think it would have to be that complicated thanks Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating dates
Take a look at Chip Pearson's instructions on how to use =datedif().
http://www.cpearson.com/excel/datedif.htm MP wrote: =(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12) is this the easiest way to calculate number of years(including fractions) between two dates? I thought =YEAR(B7)-YEAR($B$6) would have done it if b7 = 3/12/1999 and b6 = 1/12/1998 the answer is 1.00 so it's apparently just rounding the number to whole years the first formula seems to work but I didn't think it would have to be that complicated thanks Mark -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating dates
That too rounds to even number, not fractional
"y" Years The number of complete years between Date1 and Date2. same as =Year(a2)-Year(a1) Thanks though Mark I'm just surprised that it doesn't return the actual value and let the user format as required. Oh well, the one I came up with is good enough for what I need. Just thought I must be missing something simple. "Dave Peterson" wrote in message ... Take a look at Chip Pearson's instructions on how to use =datedif(). http://www.cpearson.com/excel/datedif.htm MP wrote: =(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12) is this the easiest way to calculate number of years(including fractions) between two dates? I thought =YEAR(B7)-YEAR($B$6) would have done it if b7 = 3/12/1999 and b6 = 1/12/1998 the answer is 1.00 so it's apparently just rounding the number to whole years the first formula seems to work but I didn't think it would have to be that complicated thanks Mark -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating dates
Did you see this formula on Chip's page?
=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" You could use that like: =DATEDIF(A1,NOW(),"y")+DATEDIF(A1,NOW(),"ym")/12 I stopped after months. MP wrote: That too rounds to even number, not fractional "y" Years The number of complete years between Date1 and Date2. same as =Year(a2)-Year(a1) Thanks though Mark I'm just surprised that it doesn't return the actual value and let the user format as required. Oh well, the one I came up with is good enough for what I need. Just thought I must be missing something simple. "Dave Peterson" wrote in message ... Take a look at Chip Pearson's instructions on how to use =datedif(). http://www.cpearson.com/excel/datedif.htm MP wrote: =(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12) is this the easiest way to calculate number of years(including fractions) between two dates? I thought =YEAR(B7)-YEAR($B$6) would have done it if b7 = 3/12/1999 and b6 = 1/12/1998 the answer is 1.00 so it's apparently just rounding the number to whole years the first formula seems to work but I didn't think it would have to be that complicated thanks Mark -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating dates
"MP" wrote:
=(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12) is this the easiest way to calculate number of years(including fractions) between two dates? I don't get 1.00--I get 1.16667 How accurate do you want these fractions of years to be? If you use MONTH in the formula, you'll get only multiples of 1/12 of a year in the fraction part. If you use the day difference and divide by 365 (or 365.25), you'll get closer. =(B7-B6)/365.25 The difference of the dates gives you number of days. Make sure the result cell is formatted to display a number, not a date. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating dates
"Shawn O'Donnell" wrote in message ... "MP" wrote: =(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12) is this the easiest way to calculate number of years(including fractions) between two dates? I don't get 1.00--I get 1.16667 Thats what I get with the formula =(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12) but with the formula =(YEAR(B7)-YEAR($B$6)) I get 1.00 the result cell is format Number 2 places I'll check the datediff function posted above Thanks Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating due dates | Excel Worksheet Functions | |||
calculating dates | New Users to Excel | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
I need help calculating dates? | Excel Worksheet Functions | |||
calculating dates | Excel Worksheet Functions |