Time calc

[Peter1973]

I am trying to get a simple time subtraction to work think it is my formats which are wrong

i am trying to get 23:00 - 01:25 to equal 21:35 and so on but can't seem to format it right.

Any help appreciated

 

[barry houdini]

If 23:00 is in A1 and 1:25 in B1 try just

=A1-B1

or without cell references

="23:00"-"1:25"

 

[Peter1973]

I had tried that it should work but think something in my sheet is corrupt as it keeps returning #################, have tried it in a new sheet and it works fine. Thanks for the help.

 

[barry houdini]

######### normally indicates a negative time. That might happen if your 1:25 was, in fact 25:25 [i.e. 1 day and 1:25] but formatted as h:mm. You can make sure that only the 1:25 is used with

=MOD(A1,1)-MOD(B1-1)

 

[Peter1973]

Everything is formatted as custom hh:mm, could it be that the cells are not actually in the order above ie I am actually calculating :
G4 - C4
g4=23:00 c4=00:35 so should give me 22:25.