decimal pont into time

[Peter1973]

I have 3 cells A, A2, A3 in A1 is a time in hh:mm format in cell A2 is a time in hh:mm format and in cell A3 is time but in decimal point format ie 10.75 = 10 hrs 45 mins. I am trying to find a way to calculate A3-(A2+A1) and give me the answer in hh:mm format the issue i am having is the differnet format and the 24 hr clock.

So if A1= 15:21 A2=13:48 and A3=32.25the answer would be 03:06.

any ideas

 

[Scott Huish]

=(A3/24)-(A2+A1)

Format as [h]:mm

 

[Peter1973]

Thanks

 

[Peter1973]

Is there any way it is possible to represent negative numbers also ie if A3/24 = 23:00 and A2+A1 = 44:00 then the reult would be -21:00

 

[Scott Huish]

You would have to switch the negative time to a text value, unless you switch to the 1904 date system which allows for negative time, however note that if you do this all your dates will change on your spreadsheet, so I will give you the text version:

=IF((A3/24)-(A2+A1)<0,"-"&TEXT(ABS((A3/24)-(A2+A1)),"[h]:mm"),(A3/24)-(A2+A1))