MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Negative time values??


Posted by Todd Norbury on May 05, 2001 9:03 PM

Gday all,

was just wondering if it is possible to have excel 97 show negative time values.

I have put together a spreadsheet for a few mates so as we can track our racing times. At the moment, we are all aiming for a certian time that is stored in B19. Our times are in C19-H19. I have to use the formula below to get it to output any time at all.

=IF(H19<$B19,($B19-H19),H19-$B19)

If I just have B19-H19 and the result is negative, I get an error.

I have a custom time format of m:ss.000

Any help would be greatly appreciated, more so if you could email me your answers.

mailto:norbs@optushome.com.au

Thanks.


Posted by Dave Hawley on May 05, 2001 10:51 PM


Hi Todd

Format cells Cells B19,H19 and C19 as Custom-[h]:mm:ss then in your result cell use:

=IF(B19>H19,"-" & TEXT(ABS(H19-B19),"[h]:mm:ss"),H19-B19)


..so long as you don't need to use the negative result in further calculations. if so let me know.


Dave


OzGrid Business Applications