# Time and TEXT function

#### KyleG

##### Well-known Member
I have been reading about the text function in relation to working out how long between two sets of time.

My times are never more than 24hours.
In a1 i have 7:00 am
in b1 i have 5:00 pm
in c1 i either have 0, 1 or 0.5 (meaning no break,half an hour or one hour)

D1 is where the formula goes. It needs to do as the text function does which is work out the length of time between a1 and b1 and then - the time from c1. How can i do this?

Or
in A1 i have 7:00 am
in b1 i have 5:00 pm
in c1 is 12.30 pm
in d1 is 1.30 pm

E1 is where the formula goes. It needs to do as the text function does which is work out the length of time between a1 and b1 and then - the length of time between c1 and d1. How can i do this?

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
You should be able to use this:
=+b1-A1-IF(c1=1,TIME(1,0,0),IF(c1=0.5,TIME(0,30,0),0))

Format the result as [h]:mm

Thanks for you suggestion. We came up with another solution on this end.

Replies
1
Views
585
Replies
3
Views
138
Replies
10
Views
292
Replies
3
Views
226
Replies
1
Views
93

1,203,633
Messages
6,056,450
Members
444,866
Latest member
cr130

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