sum of date ranges

[Tanya]

I have the formula to calcuate date ranges which are returned in the
following format:
C D E
2 0years 4months 9days worked in Dept. A
3 4years 2months 15days worked in Dept. B
4 28years 8months 30days worked in Dept. C

What formula would I use to calculate the total number of years worked in
all three departments?

[Biff]

Hi!

If you have dates for these time periods it would probably
be easier to something like a DATEDIF formula.

The problem arises when you try to calculate how many days
are in a month. 28,29,30,31?

=SUMPRODUCT(--(SUBSTITUTE(C2:C4,"years",""))) = 32
=SUMPRODUCT(--(SUBSTITUTE(D2:D4,"months",""))) = 14
=SUMPRODUCT(--(SUBSTITUTE(E2:E4,"days",""))) = 54

To go any further and refine the months and days you would
need to define just how many days are in a month.

Biff

>-----Original Message-----
>I have the formula to calcuate date ranges which are
returned in the
>following format:
> C D E
> 2 0years 4months 9days worked in Dept.
A
> 3 4years 2months 15days worked in Dept.
B
> 4 28years 8months 30days worked in Dept. C
>
>What formula would I use to calculate the total number of
years worked in
>all three departments?
>
>
>.
>