date computing and sum issues
 

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?
--
LAMP

date computing and sum issues
 

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

"lampatmyfeet" wrote in message
...
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?
--
LAMP

date computing and sum issues
 

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

On May 2, 7:39*pm, lampatmyfeet
wrote:
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?
--
LAMP

date computing and sum issues
 

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.
--
LAMP


"Pete_UK" wrote:

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

On May 2, 7:39 pm, lampatmyfeet
wrote:
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?
--
LAMP

date computing and sum issues
 

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

Pete

On May 5, 1:53*pm, lampatmyfeet
wrote:
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.

date computing and sum issues
 

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

"lampatmyfeet" wrote:

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?
--
LAMP

date computing and sum issues
 

You might like to look at this follow-up post from Lamp, in which he
asked for advice about totalling the years, months and days:

http://groups.google.com/group/micro...d108ef2f165444

Of course, his layout is different to yours, in that he kept the
years, months and days in separate columns.

Hope this helps.

Pete

On May 17, 5:56*pm, kathi wrote:
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 *



"lampatmyfeet" wrote:
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?
--
LAMP- Hide quoted text -

- Show quoted text -