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

JasonP91

New Member
Joined
Dec 13, 2016
Messages
13
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

Thank you in advance
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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:
Upvote 0
Alan - between 9 and 10 requires no change, so falls into the final section of the formula.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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 :p

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.
 
Upvote 0
... 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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top