Work Rota system- reading text to work out hours

Hello experts,

I am trying to create work rota system, and would really appreciate some help.

The concept is the user enters the shift into a cell (A1) either as a code ‘E’(early shift)/’L’(late Shift) or as a specific time e.g ‘2-7’. Both are in a text format. And I want the other cell (B1) to calculate the number of hours in that shift:

A1= E
B1= 8

and

A1= 2-7
B1= 5


For the first example, I have created a search function in combination with ‘if’ statements, to check to see if the user has entered either E/L. that’s working perfectly, (the last if statement is for the manual entry of the shift which I am having difficulties with):

=IF(ISNUMBER(SEARCH("E",A1)),8,IF(ISNUMBER(SEARCH("L",D9)),7,IF([HELP_NEEDED]))

The issue I am having now is trying to figure out how to determine the other shift type, which is also based on text. Ideally I need a way for the formula to read the numbers between the dash (X-Y) and allow me to manipulate the values (minus Y from X to give the hours of the shift in an integer format). But heres where it gets more tricky some times the shift could even be ‘2:30-5’. Which is a 4.5 hour shift. To do this I would have to do ‘Y – (X+ 0.2)’. is there a way of determining if one of the integers is a float?(decimal point). Therefore the dash should be the splitting point between the 2 numbers.

I do understand there could be much easier ways to do this if separate cells were used to enter the X & Y times. But the coluge I am doing this is not very computer literate and prefers to have this format. Also she tends to enter the time with a colon (2:30) would this be an issue, or could it be possible to make excel convert the text with the colon to a an integer with a decimal.

Would anyone know how to do this? Or how to go about doing this?

Thank you very much

Hi Cubb,

Welcome to the forum.
Please upload a sample workbook to support your query.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

some times the shift could even be ‘2:30-5’. Which is a 4.5 hour shift.

According to me it's 2.5 hrs shift.

anyways, try using below formula:-

Formula:

Please Login or Register  to view this content.

See attached:- work rota v1.0.xlsx

I have applied data validation to the time entry area, so now no one will be able to put time as in time with : sign.. hence 4:50 can be entered as 4.5 only

Regards,
DILIPandey

<click on below 'star' if this helps>

HI Cubb,

To achieve this you can simply use the logic like... if(formula>8,formula-1,formula).. done the same in attached workbook... thanks.

work rota v1.2.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

you are welcome cubb..

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

cid88,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.