Excel date sum help?

I'm trying to calculate length of service.


I enter a start date in A1, and enter a leaving date in A2.


If I subtract the leaving date from the start date (A3) I should get the interval between these dates (I have formatted this as years, months, and days).


However this always comes out with the correct years and days but with 1 month excess.


This occurs even if the dates are the same, showing 0 years, 1 month and 0 days.


Any ideas?

Excel date sum help?
You need the DATEDIF function - see link!





=DATEDIF (StartDate,LeavingDate,"y")%26amp;" years "


%26amp;DATEDIF (StartDate,LeavingDate,"ym")%26amp;" months "


%26amp;DATEDIF (StartDate,LeavingDate,"md")%26amp;" days"





I have had to insert a space between DATEDIF and the opening bracket, or Y!A will truncate the line...





See the link for the explanations...





The reason it doesn't work if you format the difference between two dates as a date, is that the difference between two dates is not a date, but a number of days (or months, or years)...


Conversion of days to months is inconsistant, due to the varying days per month, the DATEDIF function (ugly as it is) delivers the goods!
Reply:Yeah just tried it with the same result.


If you HAVE to have it expressed this way, perhaps use the formula =(A2-A1)-30?


Would put it out by 2 days plus or minus max
Reply:Example:


A1: 01-Feb-06 indicates Start Date (Date1)


B1: 26-Jan-08 indicates Leaving Date (Date2)





Use the DATEDIF function to compute the difference between two dates in a variety of different intervals, such number of years, months, or days.


The syntax for DATEDIF is as follows, where:





Date1 is the first date,


Date2 is the second date,


Interval is the interval type to return





C1: =DATEDIF($A1,$B1,"y")


"y" is the interval type to return years between the dates.


result: 1





D1: =DATEDIF($A1,$B1,"ym")


"ym" is the interval type to return months between the dates as if they were of the same year.


result: 11





E1: =DATEDIF($A1,$B1,"md")


"ym" is the interval type to return days between the dates as if they were of the same month and same year.


result: 25





to calculate length of service in one cell only (C1, for this example):





=DATEDIF(A1,B1, "y") %26amp; " Years, "%26amp; DATEDIF(A1,B1,"ym")%26amp; " Months, " %26amp; DATEDIF(A1,B1, "md") %26amp; " Days"





result: 1 Years, 11 Months, 25 Days





Hope this helps,


Good luck.
Reply:The basic units its using are DAYS.


Unfortunately for you, day 0 is 0/1/1900.





If you really want years months and days, then you will need a fairly complicated formula or macro to fix it.





The reason is that you will get a different number of days, depending on the start and end dates, because each month has a different number of days in it. The difference between 1/1/90 and 1/2/90 is 31 days


but from 1/2/90 to 1/3/90 is 28 days, so "1 month " has a variable number of days.





Can't you express it as days ? There's no ambiguity.


Or years with a decimal, such as 5.34 years.


Or years with a decimal "average" month 5 years 6.4 months, where a month is 365/12 (30.4) days. I know its not perfect.
Reply:Are you using the function =days360(A1,A2). This will give you the number of days between the two dates then you can simply divide by months and weeks to arrive at the exact number.