How to calculate positive and negative time

  • Thread starter Thread starter simpleandfree36
  • Start date Start date
S

simpleandfree36

I have a the following series of times in my worksheet (formatted as
general). What im trying to achieve is a total in h:mm or decimal format. Im
using 2003 excel. I have had no success with any formula ive tried. Can
anyone help me with this? it would be much appreciated.
-04:45
-03:47
00:11
00:14
-02:43
-01:10
-02:35
-01:04
00:19
-01:26
-04:32
-04:25
00:15
01:07
-01:23
-02:57
 
Excel doesn't play well with negative date/time values. When formatted as
date/time the cell will just show ##### because it doesn't know what to do
with "negative time". If you paste the text representation of a value in to a
general cell it will remain, but you can't edit it because the leading
negative makes Excel think that you have an incomplete formula.

Do you need to represent time in a way that can be manipulated/calculated,
or do you need a negative value for display?

If for diplay, I would keep a 'positive' version of your number, then
concatenate for display:
A B C
4:22 0 =if (b1 = 0, a1, "-" & text(a1,"HH:MM"))
2:12 1 [shows as] -2:22

If you need to calculate these numbers (avg number of minutes, etc) then
just format as a number (minutes will show as a decimal number, .5= noon, .25
= 6am, .75= 6pm)- do your calculations, then convert back to a date/time at
the very end.

HTH,
Keith
 
Back
Top