Trying to calculated the sum of years, months, days

[k11833]

Hello,

I need help with calculating the year, month, day from two fields. I am using the DATEDIF function to return the diffence between two dates. When I try to add the years, months, days together I get a return of 0 years 0 months 0 days. The formula I am using is

=DATEDIF(SUM(D3,G3),SUM(D3,G3),"y") & " years " &DATEDIF(SUM(D3,G3),SUM(D3,G3),"ym") & " mths " &DATEDIF(SUM(D3,G3),SUM(D3,G3),"md") & " days "

Can some one help please and the format of my spreadsheet is below.

[TABLE="width: 913"]
<tbody>[TR]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Combined Total[/TD]
[/TR]
[TR]
[TD]6-Jun-74[/TD]
[TD]31-Oct-99[/TD]
[TD]25 yrs 4 mths 25 days[/TD]
[TD]6-Dec-02[/TD]
[TD]3-Apr-14[/TD]
[TD]11 yrs 3 mths 28 days[/TD]
[TD]0 years 0 mths 0 days [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]0 yrs 0 mths 0 days[/TD]
[TD]8-Jun-88[/TD]
[TD]3-Apr-14[/TD]
[TD]25 yrs 9 mths 26 days[/TD]
[TD]0 years 0 mths 0 days [/TD]
[/TR]
[TR]
[TD]28-Feb-86[/TD]
[TD]18-Jun-00[/TD]
[TD]14 yrs 3 mths 21 days[/TD]
[TD]16-Jan-01[/TD]
[TD]3-Apr-14[/TD]
[TD]13 yrs 2 mths 18 days[/TD]
[TD]0 years 0 mths 0 days [/TD]
[/TR]
[TR]
[TD]15-Dec-98[/TD]
[TD]16-Dec-04[/TD]
[TD]6 yrs 0 mths 1 days[/TD]
[TD]20-Jul-09[/TD]
[TD]3-Apr-14[/TD]
[TD]4 yrs 8 mths 14 days[/TD]
[TD]0 years 0 mths 0 days [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]0 yrs 0 mths 0 days[/TD]
[TD]15-Mar-96[/TD]
[TD]3-Apr-14[/TD]
[TD]18 yrs 0 mths 19 days[/TD]
[TD]0 years 0 mths 0 days [/TD]
[/TR]
[TR]
[TD]4-Oct-88[/TD]
[TD]31-Oct-09[/TD]
[TD]21 yrs 0 mths 27 days[/TD]
[TD]5-Dec-11[/TD]
[TD]3-Apr-14[/TD]
[TD]2 yrs 3 mths 29 days[/TD]
[TD]0 years 0 mths 0 days [/TD]
[/TR]
[TR]
[TD]2-Jul-81[/TD]
[TD]31-Jul-01[/TD]
[TD]20 yrs 0 mths 29 days[/TD]
[TD]30-Oct-06[/TD]
[TD]3-Apr-14[/TD]
[TD]7 yrs 5 mths 4 days[/TD]
[TD]0 years 0 mths 0 days [/TD]
[/TR]
[TR]
[TD]6-Jan-76[/TD]
[TD]31-Dec-98[/TD]
[TD]22 yrs 11 mths 25 days[/TD]
[TD]2-Aug-04[/TD]
[TD]3-Apr-14[/TD]
[TD]9 yrs 8 mths 1 days[/TD]
[TD]0 years 0 mths 0 days [/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col><col></colgroup>[/TABLE]

 

[russjoliver]

Hello,

I need help with calculating the year, month, day from two fields. I am using the DATEDIF function to return the diffence between two dates. When I try to add the years, months, days together I get a return of 0 years 0 months 0 days. The formula I am using is

=DATEDIF(SUM(D3,G3),SUM(D3,G3),"y") & " years " &DATEDIF(SUM(D3,G3),SUM(D3,G3),"ym") & " mths " &DATEDIF(SUM(D3,G3),SUM(D3,G3),"md") & " days "

Can some one help please and the format of my spreadsheet is below.

[TABLE="width: 913"]
<tbody>[TR]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Combined Total[/TD]
[/TR]
[TR]
[TD]6-Jun-74[/TD]
[TD]31-Oct-99[/TD]
[TD]25 yrs 4 mths 25 days[/TD]
[TD]6-Dec-02[/TD]
[TD]3-Apr-14[/TD]
[TD]11 yrs 3 mths 28 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0 yrs 0 mths 0 days[/TD]
[TD]8-Jun-88[/TD]
[TD]3-Apr-14[/TD]
[TD]25 yrs 9 mths 26 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[/TR]
[TR]
[TD]28-Feb-86[/TD]
[TD]18-Jun-00[/TD]
[TD]14 yrs 3 mths 21 days[/TD]
[TD]16-Jan-01[/TD]
[TD]3-Apr-14[/TD]
[TD]13 yrs 2 mths 18 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[/TR]
[TR]
[TD]15-Dec-98[/TD]
[TD]16-Dec-04[/TD]
[TD]6 yrs 0 mths 1 days[/TD]
[TD]20-Jul-09[/TD]
[TD]3-Apr-14[/TD]
[TD]4 yrs 8 mths 14 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0 yrs 0 mths 0 days[/TD]
[TD]15-Mar-96[/TD]
[TD]3-Apr-14[/TD]
[TD]18 yrs 0 mths 19 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[/TR]
[TR]
[TD]4-Oct-88[/TD]
[TD]31-Oct-09[/TD]
[TD]21 yrs 0 mths 27 days[/TD]
[TD]5-Dec-11[/TD]
[TD]3-Apr-14[/TD]
[TD]2 yrs 3 mths 29 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[/TR]
[TR]
[TD]2-Jul-81[/TD]
[TD]31-Jul-01[/TD]
[TD]20 yrs 0 mths 29 days[/TD]
[TD]30-Oct-06[/TD]
[TD]3-Apr-14[/TD]
[TD]7 yrs 5 mths 4 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[/TR]
[TR]
[TD]6-Jan-76[/TD]
[TD]31-Dec-98[/TD]
[TD]22 yrs 11 mths 25 days[/TD]
[TD]2-Aug-04[/TD]
[TD]3-Apr-14[/TD]
[TD]9 yrs 8 mths 1 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

if I understand you correctly I think you are trying to use DATEDIF inappropriately. Your formula refers to Column D and Column G. Column G is the column with the formula in. DATEDIF requires 2 dates to operate correctly, so A3,B3 for instance would give you a DATEDIF. If you want to calculate the difference between the values in columns C & F, then you might need a different function rather than DATEDIF as these are strings, not dates.
I'm sorry I don't have the answer right now but this may help set you along the right path anyway. I'll be back if I get the time to look into this further.

 

[russjoliver]

This formula should give you the difference in days

=(DATEDIF(A2,B2,"D") - DATEDIF(D2,E2,"D"))
You could process this further to break down into years, months and days.

Hope this helps

Cheers

Russ

 

[russjoliver]

Which isn't what you wanted is it? You wanted the sum so that would be -

=(DATEDIF(A2,B2,"D") + DATEDIF(D2,E2,"D"))

Cheers
Russ

 

[k11833]

Thank you the formula worked =(DATEDIF(A2,B2,"D") + DATEDIF(D2,E2,"D")) , what to I need to add so that the years increase if the months go over 12 and the days increase if they go over 31.

 

[shg]

Another way:

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

Start​

[/td][td]

Finish​

[/td][td]

Total​

[/td][td]

Start​

[/td][td]

Finish​

[/td][td]

Total​

[/td][td]

Combined Total​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

06-Jun-74​

[/td][td]

31-Oct-99​

[/td][td][/td][td]

06-Dec-02​

[/td][td]

03-Apr-14​

[/td][td][/td][td]

36 years 9 months 21 days​

[/td][td]G2: =B2+E2-A2-D2[/td][/tr]

[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td]

08-Jun-88​

[/td][td]

03-Apr-14​

[/td][td][/td][td]

25 years 10 months 25 days​

[/td][td]Format of G2: y "years" m "months" d "days"[/td][/tr]

[tr][td]

4​

[/td][td]

28-Feb-86​

[/td][td]

18-Jun-00​

[/td][td][/td][td]

16-Jan-01​

[/td][td]

03-Apr-14​

[/td][td][/td][td]

27 years 7 months 6 days​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

15-Dec-98​

[/td][td]

16-Dec-04​

[/td][td][/td][td]

20-Jul-09​

[/td][td]

03-Apr-14​

[/td][td][/td][td]

10 years 9 months 15 days​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td]

15-Mar-96​

[/td][td]

03-Apr-14​

[/td][td][/td][td]

18 years 1 months 18 days​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

04-Oct-88​

[/td][td]

31-Oct-09​

[/td][td][/td][td]

05-Dec-11​

[/td][td]

03-Apr-14​

[/td][td][/td][td]

23 years 5 months 26 days​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

02-Jul-81​

[/td][td]

31-Jul-01​

[/td][td][/td][td]

30-Oct-06​

[/td][td]

03-Apr-14​

[/td][td][/td][td]

27 years 7 months 3 days​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

06-Jan-76​

[/td][td]

31-Dec-98​

[/td][td][/td][td]

02-Aug-04​

[/td][td]

03-Apr-14​

[/td][td][/td][td]

32 years 8 months 25 days​

[/td][td][/td][/tr]
[/table]

 

[k11833]

When I review the results in field G the combined total is of by +/- 1 month and a few days with the formula you are suggesting.

 

[shg]

That's because the calculation is inherently inexact; a year is 365 or 366 days, and a month is 28, 29, 30, or 31 days.

 

[russjoliver]

k11833 is right, what you are trying to do is nigh on impossible. Years vary in length, as do months. In order to be precise you'd need a starting date and a function that could use that to calulate the days, months and years between.
What you can do to help to some extent is as follows.
Adjust the formula which calculates the days between to calculate the years as follows:

=(DATEDIF(A2,B2,"Y") + DATEDIF(D2,E2,"Y"))

then months

=(DATEDIF(A2,B2,"M") + DATEDIF(D2,E2,"M"))

now if the day calculation goes in column H, year calculation in I and month calculation in J, the following formula will give you the remaining months after I years of months

=J2-(I2*12)

so by this m point you might have days - 13414, years - 36, total months - 439, months - 7. So you know it's 36 years, 7 months.
However, as the months are so variable it's not going to be possible to find out how many days should be indicated. Unless you're willing to be approximate and use 30 as the number of days in a month, but this could make things wildly inaccurate.

[TABLE="width: 962"]
<colgroup><col span="2"><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Combined Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Jun-74[/TD]
[TD="align: right"]31-Oct-99[/TD]
[TD]25 yrs 4 mths 25 days[/TD]
[TD="align: right"]06-Dec-02[/TD]
[TD="align: right"]03-Apr-14[/TD]
[TD]11 yrs 3 mths 28 days[/TD]
[TD]0 years 0 mths 0 days[/TD]
[TD="align: right"]13414[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]439[/TD]
[TD="align: right"]7

[/TD]
[/TR]
</tbody>[/TABLE]

I hope this is some help, if I can think of another way I'll let you know.