# decimal pont into time

#### Peter1973

##### Well-known Member
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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
=(A3/24)-(A2+A1)

Format as [h]:mm

Thanks

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

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))

Replies
13
Views
826
Replies
4
Views
344
Replies
5
Views
517
Replies
2
Views
545
Replies
2
Views
389

1,196,467
Messages
6,015,403
Members
441,892
Latest member
gpen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back