I need to calculate periods of service at particular employers. I have starting dates and ending dates, both in the form of month/day/year. How would I best go about setting up a process to produce the answer in the form of years, months, and days; that is to say five years, six months, and ten days?
Thanks for all answers.
The online Help should have clear examples of how to do this. In Excel 2003, the topic title is “Calculate the difference between two dates”. I don’t have 2007 available at the moment, but search for similar keywords should turn up the appropriate topic.
Months is difficult because the number of days varies from month to month. If you just want to calculate the number of days, you could do what the poster above suggests. Excel stores all dates as numbers, so straight subtraction works well.
So here is what I did. Unfortunately, this doesn’t take leap year into account, but you could add that in somewhere.
First of all, you need a 2 column table that contains your start date (column a) and end date (column b)
You also need a reference table listing the number of days in each month. In my example, column N contains the month number (1 for Jan, 2 for Feb, etc.) and column o contains the number of days in that month (31 for jan, 28 for feb, etc.)
Column c will be the elapsed years, d the elapsed months and e the elapsed days.
The formula for column C is =IF(MONTH(B5)-MONTH(A5)>=0, YEAR(B5)-YEAR(A5), YEAR(B5)-YEAR(A5)-1)
The formula for column D is =IF(DAY(B5)-DAY(A5)>=0,IF(MONTH(B5)-MONTH(A5)<0,12-ABS(MONTH(B5)-MONTH(A5)),(MONTH(B5)-MONTH(A5)-1)),IF(MONTH(B5)-MONTH(A5)-1<0,12-ABS(MONTH(B5)-MONTH(A5)-1),(MONTH(B5)-MONTH(A5)-1)))
The formula for colmu E is =IF(DAY(B5)-DAY(A5)>0,DAY(B5)-DAY(A5),VLOOKUP(MONTH(A5),$N$2:$O$13,2)-DAY(A5)+DAY(B5))
This is also assuming we are operating on the data in row 5.
There may be easier/more elegant ways, but this should do it for you!
Thanks very much; I’ll give it another shot today sometimes. Both the above suggestions are good and I appreciate them.
Here’s the qucik and dirty way I would do it: Calculate the number of days between the two dates. Divide by 365.25 and take the integral part to be the number of years. Take the remainder days, divide by 30.4 and take the integral part to be the number of months with the remainder days being just that. This is not going to be exact with the actual number of days but unless you need the very exact number it should do. in fact I would probably think fractional months would do: 2 years and 5.2 months.