time minus time - error

[stoke]

i have time formatted as custom [h]:mm

a1: 38:45 (actual time)
a2: 33:10 (planned time)
a3: =a2-a1
the result is #######

this result only occurs if the answer is negative......

i have tried various formatting/number/column width etc options with no success.

any help appreciated
Thanks!

 

[jonsi]

Hi Stoke,

Not sure what you are trying to achieve but I would go with the following formula in A3:

=IF((A2-A1)<0,(-(A2-A1)),(A2-A1))

then I'd also use conditional formatting in cell A3 to indicate whether my result time was under or not

hope this helps



hwyl
Jonsi B-)

 

[KenWright]

YOu cannot have negative times in Excel. The following link will give you an excellent overview of all the do's and donts of addition/subtraction of times within Excel:-

http://cpearson.com/excel/datearith.htm

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[Bowers74]

The following formula will return text (i.e. cannot be used in calculations), but it will show you the negative value:

=IF((A2-A1)<0,&quot;-&quot;&TEXT((-1*(A2-A1)),&quot;[hh]:mm&quot,TEXT((A2-A1),&quot;[hh]:mm&quot)

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Hi,

What about...

=ABS(A1-A2)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[stoke]

Thanks all for your efforts, although the suggestions don't give what i need (ie giving positive answers when it should be negative - Mike, yours gives the right result but as it is text it won't show in the linked graph.

What i am doing is totalling several journey times to have a total planned journey time and a total actual journey time.

i need to graph the results, and i need to show if the actual is above or below the planned time.

ie 25:10-37:30= -12:20
at this point it is hours and minutes, rather than time that i am calculating.

if i manually convert time to decimals this works but my skills aren't good enough to write a formula to convert the times into decimal numbers and therefore give me the answer i require, to show corectly on the graph.

i hope i have explained myselfwell enough!

;¬) Nathan.

 

[Bowers74]

If you want to have the time values converted to decimals (since that seems to be the only way), then use the following formula:

=VALUE(TEXT(B1-A1,&quot;General&quot)

Be sure that the cell's format is &quot;General&quot; before entering the formula, or change it afterwards, because Excel will change it to &quot;hh:mm&quot; format automatically! You won't be able to display the negative values in the graph though unless you use my first suggestion next to the above formula, and use those values as the data label.

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!