# date computing and sum issues

L

#### lampatmyfeet

I need to compute the number of years, months and days between dates. After
doing so: if days >= 15 then add 1 to month and days becomes 0, then if
months > 9 add 1 to year and then months becomes 0.

I will need to do this for several rows and them sum up the individual columns

My format is as follows:

A B C D E F G
Date 1 thru Date 2 = yy mm dd
Date 3 thru Date 4 = yy mm dd
..
..
..
TOTAL YY MM DD

the numbers is columns E,F & G are the differances between dates 2 and 1
Date X is formated at follows mm/dd/yyyy

I have tried several formulas but seem to leave out some component. My
latest iteration for computing years is
=YEAR(C9)-YEAR(A9)-IF(OR(MONTH(C9)<MONTH(A9),AND(MONTH(C9)=MONTH(A9),DAY(C9)<DAY(A9))),1,0)&""
but this formula will not allow me to total the column and the problem is
the same for the months and day columns.

Any help?

Your rounding techniques will lead to wildly incorrect values.

Look at the DATEDIF function: the best summary is at

http://www.cpearson.com/excel/datedif.htm

Another technique would be to use a formula like

=SUMPRODUCT((C2:C10-A2:A10)*1)

and format that cell for YY MM DD

HTH,
Bernie

I assume your dates are in A9 and C9, so let's start with the simplest
expression and put this in G9 for the days:

=IF(DATEDIF(A9,C9,"md")>=15,0,DATEDIF(A9,C9,"md"))

It will return 0 if there are more than 14 days difference.

Now put this in E9:

=IF(DATEDIF(A9,C9,"ym")
+IF(DATEDIF(A9,C9,"md")>=15,1,0)>=10,0,DATEDIF(A9,C9,"ym")
+IF(DATEDIF(A9,C9,"md")>=15,1,0))

We have to take account of any carry forward from G, and if the number
of months is greater than 9 then this will show 0.

Finally, put this formula in D9:

=DATEDIF(A9,C9,"y")+IF(DATEDIF(A9,C9,"ym")
+IF(DATEDIF(A9,C9,"md")>=15,1,0)>=10,1,0)

This takes account of any carry forward from E.

You will need to apply similar logic for the overall totals.

Hope this helps.

Pete

Pete,

Thanks so much for the help, I just got back to the office and tried your
formulas: they work great. Was not familiar with "DATEDIF" but will file
that one in the archives.

Bernie,

I appreciate the link to the cpearson site and have read up on the function
also.

Thanks for feeding back, Lamp - glad to hear the formulae worked for
you.

Pete

I have an excell spreadsheet that does this in two ways....
1) =A2-A1 then format the cell to custom typing in yy " years, " mm "
months, " dd " days"
OR
2) =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, " &
DATEDIF(A2,B2,"md") & " days"

My problem is that now I need to total all of those results into a TOTAL
YEARS SPENT