Selecting and Summing Date Values

**controlfreak** · 06-08-2009, 10:29 AM

Hi guys,

I have an excel sheet (see attached) that i will be pasting columns A to G in every month. What i need to do is for Excel to

1. Automatically provide a unique list of Approver ID (i can already do this with Advanced Filter)
2. Count how many of this unique entry there is (can do this already)
3. Then to find and sum all the time differences for that approver so a total approval time taken time is outputted for each person.

Any help is appreciated!

**ChemistB** · 06-08-2009, 10:59 AM

Your time columns are strings (text). I would create a "Total Time" column using a format with format cell>Number>Custom [h]:mm. In that column, beginning with row 2

Please Login or Register  to view this content.

Does that work for you?

**controlfreak** · 06-08-2009, 11:03 AM

Hi ChemistB

I can exract the date serial number using a DATEVALUE function but i need to add the difference in these datevalues and create a total amount for each approver in my unique list.
does this clarify my question?
i'm not great at explaining these!

**ChemistB** · 06-08-2009, 11:31 AM

Once you have your time differences (let's put those in column H), and move your unique Approver list over two to J(I like the space to clarify it's a different table), can you use SUMPRODUCT like so

Please Login or Register  to view this content.

Format the cells appropriately. Does that work for you?

**controlfreak** · 06-08-2009, 12:06 PM

Hey that worked dude! Thanks a mill

I got my processing times in column H and dragged them down. I pasted the formula into L2 and it's given me a number of 64.2.
Is this the total processing time so for the operator in J2?

When i'm dragging the formula down its incrementing the cell range and giving a #VALUE! error because of this.
Is there a way i can drag the formula down while still keeping the $H$2:$H4314 bit as it is? Or is there a way to change the formula to be more general to accept any newly pasted list that may be longer than the list i uploaded (can the formula refer to whole columns and not just discrete ranges)?

**ChemistB** · 06-08-2009, 01:55 PM

Is there a way i can drag the formula down while still keeping the $H$2:$H4314 bit as it is

Sorry, it should be $H$2:$H$4314. I missed one "$". Those act as anchors to keep the range from moving. Glad it's working for you.

**controlfreak** · 06-09-2009, 03:24 AM

thanks for the help!

so if i was to be pasting new data in can i leave off the dollar signs to make it more dynamic to the new data?

**ChemistB** · 06-09-2009, 10:07 AM

I wouldn't leave off the dollar signs as when you drag it down, your ranges will constantly change. You don't want that. You can use a larger number to start with (not too big or it will slow up your system). Maybe $H$2:$H$5000?. You could also use named dynamic ranges

http://www.contextures.com/xlNames01.html#Dynamic