Time Calculations

Exactly what format do you want Excel to return the data in??

Compliments of daddylonglegs:

Row\Col	D	E	F
2	1h 06m 44s	1:06:44	E2: =SUM(MID(0 & D2 & "0000", SEARCH({"D","H","M","S"}, D2 & "xxDHMS")-1, 2) / {1,24,1440,86400})
3	21m 43s	0:21:43
4	07m 58s	0:07:58
5	00m 00s	0:00:00
6	00m 00s	0:00:00
7	00m 00s	0:00:00
8	1h 42m 55s	1:42:55
9	00m 00s	0:00:00
10	1h 51m 11s	1:51:11
11	31m 43s	0:31:43
12	1h 48m 31s	1:48:31

I used the following format in Column H with a cell format of [hh]:mm:ss

=IF(ISNUMBER(SEARCH("h",E2)),LEFT(E2,SEARCH("h",E2)-1)/24,0)+MID(E2,SEARCH("m",E2)-2,2)/(60*24)+MID(E2,SEARCH("s",E2)-2,2)/(60*60*24)
Would this work for you?

If you are going to format your results as time, the Excel sees time as a subset of a day (makes sense) and uses the day as the primary unit. 1 in excel = 1 day. So when you are working with whole numbers but want them to be hours, 1 hour = 1/24 of a day so you divide by 24. 6 h = 0.25
The same goes for minutes and seconds. Does that help?

Yessir - thanks for the clarification...so if I wanted the result to be just seconds as a number (or minutes in a decimal value) so instead of 00:16:41 it'd just be 1001 seconds or 16.68?

I guess I could just go and utilize text-to-columns at this point to split out with a : as the separator, then run the math...but it'd be handy if I could do that all in the formula...

Once you've calculated it into the proper fraction of a day, then it's all up to how you want to format it. So let's say A1 contains 0.25

If we go to cell format>custom and format as hh:mm we'll see 06:00
if we format as h, we'll see 6
if we format as mm, we'll see 01 (WELL, that's not right!) What Excel does with time is moves it into the proper positions. i.e. 60 minutes = 1 hour and 0 minutes, so we need to show all the units (format as dd:hh:mm:ss for example) or use [] to tell Excel that you want to top out at that unit.

Soooo, back to the drawing board
if we format as [mm], we'll see 360
If A2 = 8.2 * A1, then
if we format as hh:mm, we'll see 1:12 (because 6 hours * 8.2 = 2 days 1 hour 12 minutes)
if we format as [hh]:mm, we'll see 49:12

Make sense?

To report everything as minutes, I would just convert everything to minutes.
1 day = 24*60 minutes =1440
so take the time * 14440 and format the cell as general.
You could even do the calculations within the pivot table as a calculated field

That doesn't work because even with a custom setup as [mm]:ss the actual value in the field shows as a time value (with that ridiculous AM or PM in there that would then screw up any sort of text to column function. I've found a much easier way...

Step 1: Got a character count using =len(A2) in a new column
Step 2: Sorted the new column in decreasing order - so now all the cells with an hour value (say 1h 22m 15s) would be set at the top
Step 3: Text to column with space as the delimiter
Step 4: Find/Replace to remove the "h" "m" and "s" from the new resulting set of just that cell range
Step 5: Add a new column and run a formula on the previous 3 columns i.e. E2*60+F2+G2/60 and I got the value I ultimately was looking for

Funny how the fancy complicated formulas from earlier were taking me a longer time to try reformatting than this way...