Calculating date ranges

[Guest]

I'm using the following functions to calculate a date range in a row:
Years Months
Days
=YEAR(B5)-YEAR(A5) =(MOD((B3-A3),365.25))/30.4 =DAY(B3-A3)

But if I have a group of date ranges, such as
12/01/77 04/21/78
04/22/78 07/06/78
07/07/80 03/28/84
03/29/84 05/13/84

What would be the formula to calculate the sum of the return of the years,
months days. If I use the SUM function, I'll get something like 71 months.
I'm trying to calculate creditable service.

 

[Arvi Laanemets]

Hi

=DATEDIF(A5,B5,"Y")
=DATEDIF(A5,B5,"M")
=DATEDIF(A5,B5,"YM")
=DATEDIF(A5,B5,"MD")

 

[Guest]

Hi Arvil,

That's not working.

If I have the following info in a spreadsheet:

 

[Guest]

5Sorry,

If I have the following information:
Years Months Days
12/01/77 04/21/78 1 5 20
04/22/78 07/06/78 0 2 15
07/07/80 03/28/84 4 9 21

If I use the sum function I'll get 5 years, 16 mos and 56 days which is
acutally 6 yrs, 5 mos and 26 days. What formula would I use to get ther
correct years of services.

 

[Myrna Larson]

With the first date in A1, the 2nd in B1

=DATEDIF(A1,B1,"Y")&" years, " &DATEDIF(A1,B1,"YM") &" months and "
&DATEDIF(A1,B1,"MD")

 

[Guest]

I'm sorry, maybe I'm not being clear. I'm trying to calculate the
information in the rows. Example
Years Months Days
12/01/77 04/21/78 1 5 20
04/22/78 07/06/78 0 2 15
07/07/80 03/28/84 4 9 21
What would be the formula here
to get an accurage sum of years, months and days without getting 5 yrs, 16mos
and 56 days?

 

[Arvi Laanemets]

Hi

Are dates really 'adjacent'? When there are no gaps between date intervals,
then with start and end dates p.e. in range A5:B5
Years: =DATEDIF(MIN(A5:B7),MAX(A5:B7),"Y")
Months: =DATEDIF(MIN(A5:B7),MAX(A5:B7),"YM")
Days: =DATEDIF(MIN(A5:B7),MAX(A5:B7),"MD")

Or simply
Years: =DATEDIF(A5;B7,"Y")
Months: =DATEDIF(A5;B7,"YM")
Days: =DATEDIF(A5;B7,"MD")

This must return 6 years 3 months and 27 days

(In your example is something wrong with your calculations. There must be
Years Months Days
0 4 20
0 2 14
5 8 21)

When really there are gaps between date intervals, there is in princip no
way to estimate exactly the total number of years, months and especially
days - because months lengths vary. The only accetable way is estimate it.
There are several ways for it - i prefer the one, where the average length
of year is 365.25 days, and average length of month is 365.25/12 days. This
is usually enough to estimate years and months, at least when the number of
summarized days is high enough, but hardly days. But no other method is
significally better too.

Formulas for this case will be
Years: =INT((SUM(B1:B3)-SUM(A1:A3))/365.25)
Months: =INT(MOD((SUM(B1:B3)-SUM(A1:A3)),365.25)/(365.25/12))
Days: =INT(MOD(MOD((SUM(B1:B3)-SUM(A1:A3)),365.25),(365.25/12)))

First 2 formulas return same result as ones above. The last one returns 24
days - as I sayd you can't calculate remaining days exactly.


Arvi Laanemets

 

[Myrna Larson]

If what you show is actual dates and the values you've calculated for years,
months, and days, there's a problem with your arithmetic.

For the first time span, the interval is 0 years, 4 months, and 20 days (not
1, 5, and 20); for the 2nd, it's 0, 2, and 14 (not 0, 2, and 15), and for the
3rd it's 3, 8, and 21 (not 4, 9 and 21). The correct totals are 3, 14, and 55,
or 4 years, 3 months, and 25 (or 24 or 27) days, depending on how many days
there are in the "final" month.

The problem with this kind of calculation is the number of days in a month. If
you can compromise on 30 days per month, with the corrected results, years in
column C, months in D, and days in E, one simple way is to add another column,
F, that contains the formula =DAYS360(A1,B1), and copy down through F3.

In F4, write the formula =SUM(F1:F3)
in C4, for the years: =INT(F4/360)
in D4, for months: =MOD(INT(F4/30),12)
in E4, for the days: =MOD(F4,30)

This gives the values 4, 3, and 25 in cells C4:E4.

In fact, if you don't need the years, months and days for each interval, a
single column with the DAYS360 formula may be all that's needed.

 

[Myrna Larson]

Arvi was observant enough to see that in fact your three intervals were
consecutive, with no gaps between them. If that is always the case, and the
intervals are in chronological order, you should be able to eliminate the MIN
and MAX formulas and just use DATEDIF with the first starting date as the 1st
argument and the last ending date as the last argument. But if there are gaps,
this will not be correct.

 

[Guest]

I used the datedif formula and the and it returned more accurate
calculations. Most of the date ranges are not adjacent. Once I entered in
all of my dates I simply added up all of the days and divided by 30.4, the
months by 12 and adjusted the total number of years of service. Thanks for
all of your help and patience!