Negative times

[djdiplomat]

Hello all

I've been given some data from a payroll system. The trouble is, adjustments have been made using negative times e.g. -0:15 for minus 15 minutes.

Excel doesn't see these as times (think it must see as text) so any calcs made to these just give errors.

Does anyone know how I could convert these negative text times to a positive correct time e.g. -0:15 would become 00:15 (like ABS time?)

Any help would be greatly appreciated!

Thanks

Will

 

[mikerickson]

Have you tried =TIMEVALUE(SUBSTITUTE(A1,"-",""))

 

[djdiplomat]

Have you tried =TIMEVALUE(SUBSTITUTE(A1,"-",""))

That's great, thanks - works a treat!

I had made a lookup table for each negative time to get it working but your method is much quicker!