Calculating Total Time

Thanks in advance for any help this community can provide. I've been tasked to come up with an excel sheet that does two main functions. I've been able to complete the first but the second one is really giving me a hard time.

Basically I need to take the start date for each employee and display the total number of days they have been affiliated with the company. For example I started 05/28/96 so I have 11 years, 10 months, XX days. I have been able to do this using VBA (but if I could figure out a way to get rid of the #VALUE that would be awesome).

The next thing that I need to do that I need help with is take everyone's total time that they have been working at the company and add it together. Ultimate goal is to show the total experience that can be utilized.

There is one requirement. The date format HAS to be entered in yyyymmdd format. If you look at my example I couldn't figure out how to get EXCEL to deal with this so I converted it using Column C. This column will probably be hidden from the end user.

I have attached a copy of my working example. If anyone could help me accomplish this it would be awesome. I'm open to suggestions if anyone has a better idea then using the VBA that I have here.

Maybe like this:

Please Login or Register  to view this content.

C3 and down: =VALUE(TEXT(B3, "0000-00-00"))

D3 and down: =DATEDIF(C3, $A$1, "y") & " yrs " & DATEDIF(C3, $A$1, "ym") & " mos " & DATEDIF(C3, $A$1, "md") & " days"

C15 (array formula): =TODAY() - SUM(TODAY() - C3:C12)

D15: =DATEDIF($A$1, C15, "y") & " yrs " & DATEDIF($A$1, C15, "ym") & " mos " & DATEDIF($A$1, C15, "md") & " days"

Thanks a TON. This is perfect. EXACTLY what I needed. I have one more question though. I can't get the formula in D15 to work. I keep getting the #NUM! error.

Any ideas on what I'm doing wrong. I've attached my working document for reference.

Originally Posted by SailorGuy

Thanks a TON. This is perfect. EXACTLY what I needed. I have one more question though. I can't get the formula in D15 to work. I keep getting the #NUM! error.

Any ideas on what I'm doing wrong. I've attached my working document for reference.

If you just need the total experience expressed as a number of days, inclusive of the first and last day then use:

Please Login or Register  to view this content.

similarly if you need each person's service in days use:

Please Login or Register  to view this content.

The simple number of days, rather than text describing the service, may be easier to work with in any future analysis.

Rgds


HTH