sum of date ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculating service dates 2
not sure what formula to use? IF??? 1
Not sure what formula to use? If?? 1
Complex SUM *** Variant of previous post 8
Lookup 3 matching values of which one is a range and return a sum 2
Add values based on text color with the criteria of a date 7
Complex SUM 4
Automatically chnage number format of a range of cells 2

Back
Top