Adding Date Formula

[Catfish]

Hello, I have these two monster formulas that I want to add together to get
a grand total of time.

One is:

=YEAR(C9)-YEAR(C8)-IF(OR(MONTH(C9)<MONTH(C8),AND(MONTH(C9)=MONTH(C8),
DAY(C9)<DAY(C8))),1,0)&" years, "&MONTH(C9)-MONTH(C8)+IF(AND(MONTH(C9)
<=MONTH(C8),DAY(C9)<DAY(C8)),11,IF(AND(MONTH(C9)<MONTH(C8),DAY(C9)

=DAY(C8)),12,IF(AND(MONTH(C9)>MONTH(C8),DAY(C9)<DAY(C8)),-1)))&" months,

"&C9-DATE(YEAR(C9),MONTH(C9)-IF(DAY(C9)<DAY(C8),1,0),DAY(C8))&" days"

I want to add the above with:

=YEAR(C12)-YEAR(C11)-IF(OR(MONTH(C12)<MONTH(C11),AND(MONTH(C12)=MONTH(C11),
DAY(C12)<DAY(C11))),1,0)&" years, "&MONTH(C12)-MONTH(C11)+IF(AND(MONTH(C12)
<=MONTH(C11),DAY(C12)<DAY(C11)),11,IF(AND(MONTH(C12)<MONTH(C11),DAY(C12)

=DAY(C11)),12,IF(AND(MONTH(C12)>MONTH(C11),DAY(C12)<DAY(C11)),-1)))&"

months,
"&C12-DATE(YEAR(C12),MONTH(C12)-IF(DAY(C12)<DAY(C11),1,0),DAY(C11))&" days"

to get a total of the two.

I have a date in cell C8 and in C9 and then next dates entered are in cell
C11 and C12.

Thanks!

 

[Ron Rosenfeld]

Hello, I have these two monster formulas that I want to add together to get
a grand total of time.

One is:

=YEAR(C9)-YEAR(C8)-IF(OR(MONTH(C9)<MONTH(C8),AND(MONTH(C9)=MONTH(C8),
DAY(C9)<DAY(C8))),1,0)&" years, "&MONTH(C9)-MONTH(C8)+IF(AND(MONTH(C9)
<=MONTH(C8),DAY(C9)<DAY(C8)),11,IF(AND(MONTH(C9)<MONTH(C8),DAY(C9)
"&C9-DATE(YEAR(C9),MONTH(C9)-IF(DAY(C9)<DAY(C8),1,0),DAY(C8))&" days"

I want to add the above with:

=YEAR(C12)-YEAR(C11)-IF(OR(MONTH(C12)<MONTH(C11),AND(MONTH(C12)=MONTH(C11),
DAY(C12)<DAY(C11))),1,0)&" years, "&MONTH(C12)-MONTH(C11)+IF(AND(MONTH(C12)
<=MONTH(C11),DAY(C12)<DAY(C11)),11,IF(AND(MONTH(C12)<MONTH(C11),DAY(C12)
months,
"&C12-DATE(YEAR(C12),MONTH(C12)-IF(DAY(C12)<DAY(C11),1,0),DAY(C11))&" days"

to get a total of the two.

I have a date in cell C8 and in C9 and then next dates entered are in cell
C11 and C12.

Thanks!

Perhaps if you could describe in words exactly what you are trying to do with
the dates in C8 C9 C11 C12 someone might be able to come up with a simpler
formula.

For example, it appears as if your first formula can be duplicated by using:

=DATEDIF(C8,C9,"y")&" years "&
DATEDIF(C8,C9,"m")&" months "&
DATEDIF(C8,C9,"md")&" days"

It's shorter and possible easier to understand. It also has the same errors as
your formula:

C8: 31 Jan 2007
C9: 1 Mar 2007

Your formula:

"0 years, 1 months, -2 days"

DATEDIF formula:

0 years 1 months -2 days

Both also do not change plural/singular for the years/months/days.

To add the two time intervals, though, you'd need to make some decisions about
lengths of a month and year.

What if, for example, you came out with the following:

First pair:
1 years, 11 months, 16 days

Second pair:
0 years, 5 months, 15 days

Since a month can have 28-31 days, there are several possible solutions.

So it really depends on what you want to do.

The simplest method might be to just add up the number of days, and then define
a year as 365.25 days, a month as 365.25/12 days, and use the remainder left
over as days.

But there are other possible methods.




--ron

 

[Catfish]

I want to calculate the following:
cell C8 = 08/03/1981 starting date
cell C9 = 09/01/2002 ending date

add the above date result with

cell C11 = 05/15/2006 restarting date
cell C12 = Today's date.

With my formula the top result was 21 years, 0 months, 30 days
next was 1 years, 5 months, 0 days

I'd like to add/combine these two somehow. Maybe just one formula minus the
missing days between.
The result with years, months, and days is nice.

The huge formula was one found online and not mine. A simplar one would be
nice!
I'm looking at your reply below for additional info.

Thank you so much for replying!

Mike

 

[Ron Rosenfeld]

I want to calculate the following:
cell C8 = 08/03/1981 starting date
cell C9 = 09/01/2002 ending date

add the above date result with

cell C11 = 05/15/2006 restarting date
cell C12 = Today's date.

With my formula the top result was 21 years, 0 months, 30 days
next was 1 years, 5 months, 0 days

I'd like to add/combine these two somehow. Maybe just one formula minus the
missing days between.
The result with years, months, and days is nice.

The huge formula was one found online and not mine. A simplar one would be
nice!
I'm looking at your reply below for additional info.

Thank you so much for replying!

Mike

Several comments.

First of all, the DATEDIFF formula I gave was slightly wrong. It should be:

=DATEDIF(C8,C9,"y")&" years "&
DATEDIF(C8,C9,"ym")&" months "&
DATEDIF(C8,C9,"md")&" days"

(The error was in line 2 where "m" should have been "ym" as above).

Second: I get different results than what you wrote for the dates using your
formula as posted:

21 years 0 months 29 days
1 years 5 months 1 days


Either you did not post the results you have, or perhaps you are also using
times in C8-C11 and that is throwing things off.

Third, and most important, you still need to decide what you want to do about
defining "month" and "year". See my previous post.
--ron

 

[Catfish]

Well what about using your datedif forumla example only taking my original
C12 Today's date - C8 date
then subtract 1351 days inbetween the C9 and C11 date? This may not be
possible.

I understand what
you were talking about on the defining months and days since there are
different number of days in months.

Your formula is so much simpler than my original one.

Thanks again!

 

[Ron Rosenfeld]

Well what about using your datedif forumla example only taking my original
C12 Today's date - C8 date
then subtract 1351 days inbetween the C9 and C11 date? This may not be
possible.

I understand what
you were talking about on the defining months and days since there are
different number of days in months.

Your formula is so much simpler than my original one.

Thanks again!

If you are just going to look at the numbers of elapsed days, it would be
simply:

=C9-C8 + C12 - C11

Format the result as "General"

For your values, I get 8,218 days.

You could then use the following formulas:

A1: =C9-C8+C12-C11

A3: =INT(A1/365.25)
Format/Cells/Number/Custom Type: 0" years"

A4: =INT((A1-A3*365.25)/(365.25/12))
Format/Cells/Number/Custom Type: 0" months"

A5: =INT(A1-A3*365.25-A4*365.25/12)
Format/Cells/Number/Custom Type: 0" days"

Or something similar, depending on how you wanted to define months and years.

You could also put it all into a single cell and output a text string, but the
formula is "messy":

=TEXT(INT((C9-C8+C12-C11)/365.25),"0"" yrs""")&
TEXT(INT((C9-C8+C12-C11-INT((C9-C8+C12-C11)
/365.25)*365.25)/(365.25/12)),"\, 0 ""months""") &
TEXT(INT(C9-C8+C12-C11-INT((C9-C8+C12-C11)/
365.25)*365.25-INT((C9-C8+C12-C11-A3*365.25)/
(365.25/12))*365.25/12),"\, 0 ""days""")



--ron

 

[Catfish]

You Rock!

Worked great.

Thank you sir! I owe ya the biggest hamburger in Texas!

 

[Ron Rosenfeld]

You Rock!

Worked great.

Thank you sir! I owe ya the biggest hamburger in Texas!

You're on! (The next time I'm in Texas )


--ron