# Combine Formulas - Formula to type time without colons + existing time based formula

#### JasonP91

##### New Member
I recently had help creating a formula so for a work rota, so that a certain amount of time is subtracted from different lengths of hours - this post http://www.mrexcel.com/forum/excel-questions/980512-nested-if-time-functions-timesheet-rota-2.html

I'm now using this formula: =IF((C4-B4)<TIME(6,0,0),C4-B4,IF(AND((C4-B4)>=TIME(6,0,0),(C4-B4)<=TIME(7,0,0)),(C4-B4)-TIME(0,30,0),IF(AND(C4-B4>=TIME(8,0,0),C4-B4<=TIME(9,0,0)),(C4-B4)-TIME(1,0,0),IF(C4-B4>=TIME(10,0,0),(C4-B4)-TIME(1,30,0),C4-B4))))

I'm now searching for a way to input the time into the spreadsheet without colons. I came across formatting the cell to "00\:00", but that now messes with the formula used, as it's reported as text, not time now. I then came across using this formula, which re-enables the addition/subtraction of time:

TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4)

I'm now looking for a way to merge them together, enabling me to type time without colons, and for the relevant maths to work out. Any help will be greatly appreciated.

Here is a link to the speadsheet I'm working on: https://www.dropbox.com/s/pz8ihcg53ltbgdd/Jason Rota Fuction Test dropbox.xlsm?dl=0

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### AlanY

##### Well-known Member
your formula missed out the bit when (C4-B4) is between 9 and 10,

=IF((C4-B4)=TIME(6,0,0),(C4-B4)<=TIME(7,0,0)),(C4-B4)-TIME(0,30,0),IF(AND(C4-B4>=TIME(8,0,0),C4-B4<=TIME(9,0,0)),(C4-B4)-TIME(1,0,0),IF(C4-B4>=TIME(10,0,0),(C4-B4)-TIME(1,30,0),C4-B4))))

if that also minus 1hr30mins then formula can be simplified as

=IF(C4-B4< TIME(6,0,0),C4-B4,IF(C4-B4<=TIME(7,0,0),C4-B4-TIME(0,30,0),IF(C4-B4<=TIME(9,0,0),C4-B4-TIME(1,0,0),C4-B4-TIME(1,30,0))))

as re the colons, one solution is to have an extra helper column that can be hidden

Excel 2012
ABCDEF
1
2
3
4120017300.2305:30
5120018300.2706:00
6120019300.3106:30
7120020300.3507:30
8120021300.4008:00
9120022300.4409:00
10120023300.4810:00
11
Sheet2
Cell Formulas
RangeFormula
D4=(TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4))
E4=IF(D46,0,0),D4,IF(D4<=TIME(7,0,0),D4-TIME(0,30,0),IF(D4<=TIME(9,0,0),D4-TIME(1,0,0),D4-TIME(1,30,0))))

Last edited:

#### AliGW

##### Banned
Alan - between 9 and 10 requires no change, so falls into the final section of the formula.

#### AlanY

##### Well-known Member
confused

for C4=9:00 and D4=18:30, what will be the result?

no change as D4-C4 = 9h30m?

Excel 2012
ABCD
1Date18.12.16
2NameSunday
3Management
4NAME09:0017:3007:30
5NAME09:0018:3009:30
6NAME09:0019:3009:00
Sheet1
Cell Formulas
RangeFormula
D4=IF((C4-B4)6,0,0),C4-B4,IF(AND((C4-B4)>=TIME(6,0,0),(C4-B4)<=TIME(7,0,0)),(C4-B4)-TIME(0,30,0),IF(AND(C4-B4>=TIME(8,0,0),C4-B4<=TIME(9,0,0)),(C4-B4)-TIME(1,0,0),IF(C4-B4>=TIME(10,0,0),(C4-B4)-TIME(1,30,0),C4-B4))))

Last edited:

#### AliGW

##### Banned
To be honest, a nested IF statement is not how I would have done it, but that is what Jason was working with. Some sort of lookup table would be better.

#### Tetra201

##### MrExcel MVP
Is this what you need?

=(TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4))-LOOKUP(((TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4)))*24,{0,5,7,10},{0,0.5,1,1.5})/24

#### AlanY

##### Well-known Member
Is this what you need?

=(TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4))-LOOKUP(((TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4)))*24,{0,5,7,10},{0,0.5,1,1.5})/24

neat solution, Jason please check the requirement between 9:30 and 18:30 (highlighted), i.e. would that be -1 or -1.5

Excel 2012
ABCDEFGH
1
2
3AlanTetra
490017300.3507:3007:30
590018300.4008:0008:30
690019300.4409:0009:00
790020300.4810:0010:00
890021300.5211:0011:00
990022300.5612:0012:00
10120023300.4810:0010:00
11
Sheet2
Cell Formulas
RangeFormula
D5=(TEXT(C5,"00\:00")-TEXT(B5,"00\:00")+(B5>C5))
E5=IF(D56,0,0),D5,IF(D5<=TIME(7,0,0),D5-TIME(0,30,0),IF(D5<=TIME(9,0,0),D5-TIME(1,0,0),D5-TIME(1,30,0))))
G5=(TEXT(C5,"00\:00")-TEXT(B5,"00\:00")+(B5>C5))-LOOKUP(((TEXT(C5,"00\:00")-TEXT(B5,"00\:00")+(B5>C5)))*24,{0,5,7,10},{0,0.5,1,1.5})/24

Last edited:

#### Tetra201

##### MrExcel MVP
I assumed the following subtraction schedule:
<5:00 --> 0:00
5:00 to <7:00 --> 0:30
7:00 to <10:00 --> 1:00
10:00 and up --> 1:30

The formula can be easily adjusted to a different schedule.

#### JasonP91

##### New Member
Thank you all so much for your help. The schedules are as follows,
If they work 10+ hours, 1h30mins is taken off,
8+, 1h is taken off,
6+ 30mins taken off
Less than 6, nothing.
I'll test what has been posted asap, travelling at the minute but will report back later.

Bit of background for it, the current person who does the rota has a lot of trouble with it, so I'm trying to make it as simple as possible, just needs to type in the working hours and everything else is done for him. The layout etc needs to stay roughly the same as its done at a higher level, but in hoping if I (through all your help) can get this working, I can bump it up the ladder. But it turns out I forgot more about excel than I realised

The end result simply needs to be so the hours can all be typed in without needing to type colon every time, breaks to be automatically deducted and then everything to total. If anyone knows any better way than all these long formulas and more than willing to look at a different way, it just needs to stay in the visual style it currently is.

#### Tetra201

##### MrExcel MVP
... The schedules are as follows,
If they work 10+ hours, 1h30mins is taken off,
8+, 1h is taken off,
6+ 30mins taken off
Less than 6, nothing.
Here is an updated formula:

=(TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4))-LOOKUP(((TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4)))*24,{0,6,8,10},{0,0.5,1,1.5})/24

Replies
0
Views
399
Replies
4
Views
704
Replies
6
Views
976
Replies
5
Views
484
Replies
8
Views
788

1,190,786
Messages
5,982,916
Members
439,807
Latest member
WXM86

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