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

[JasonP91]

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

 

[JasonP91]

Thank you so much! That works perfectly! Really appreciate all the help!