date computing and sum issues

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lampatmyfeet - 02 May 2008 19:39 GMT

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?

Signature

LAMP

Reply to this Message

Bernie Deitrick - 03 May 2008 01:10 GMT

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 need to compute the number of years, months and days between dates.
>After
[quoted text clipped - 24 lines]
>
> Any help?

Reply to this Message

Pete_UK - 03 May 2008 01:20 GMT

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
<lampatmyf...@discussions.microsoft.com> 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
[quoted text clipped - 24 lines]
> --
> LAMP

Reply to this Message

lampatmyfeet - 05 May 2008 13:53 GMT

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.

Signature

LAMP

> 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:
[quoted text clipped - 55 lines]
> > --
> > LAMP

Reply to this Message

Pete_UK - 06 May 2008 01:40 GMT

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

Pete

On May 5, 1:53 pm, lampatmyfeet
<lampatmyf...@discussions.microsoft.com> 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.

Reply to this Message

kathi - 17 May 2008 17:56 GMT

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

> 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
[quoted text clipped - 22 lines]
>
> Any help?

Reply to this Message

Pete_UK - 17 May 2008 18:41 GMT

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/microsoft.public.excel.worksheet.functions/browse
_frm/thread/a2d73a6151c833bc/e1d108ef2f165444?lnk=st&q=#e1d108ef2f165444

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

Hope this helps.

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 "
[quoted text clipped - 36 lines]
>
> - Show quoted text -

Reply to this Message