Formatting a total time entry and building a cumulative time formula.

**TMc10** · 12-16-2012, 07:56 PM

I use the format of time entry in the first line below. The text formulas give me elapsed time. The if formulas give me time in a format I can add to get a total time over several days. I need to insert a time total from previous years like 52363:10 (52363 hours and 10 min) and start keeping a cumulative time in addition to my current entries.

8/30/2012 19:50 8/30/2012 20:08 8/30/2012 20:28 8/30/2012 20:35
0:20 0:45 0:20 0:45

=TEXT(I292-H292,"h:mm") =TEXT(J292-G292,"h:mm")
=IF(ISBLANK(L292),"",TIMEVALUE(L292)) =IF(ISBLANK(M292),"",TIMEVALUE(M292))

Thank you for your assistance.

**daddylonglegs** · 12-16-2012, 09:03 PM

By using TEXT function you are converting to text....and then you need to convert that back to a time, better to use a simple subtraction like

=I292-H292

then using cell formatting to display as required, e.g. format result cell as h:mm

Excel doesn't recognise input time values that are over 9999:59:59 (although it can still return higher time values as a formula result so if you need to add 52363:10 then one way is to get that value in a cell as a calculation of some sort using time values < 10:000:00, e.g. try this

=10*"5000:00"+"2363:10"

format result cell as [h]:mm

that will give you 52363:10 [if you just type 52363:10 in a cell it will look the same as that but excel treats it as text, not time, hence this approach]

You can then add that cell value to whatever other time totals you need