Need to "read" a cell, and the concert the contents into a math calculation and solve.

[infadibulum]

So basically I'm making a staff roster for my cafe in which I'd like to calculate the hours automatically.
https://1drv.ms/x/s!Aje5ltYIGNV8gexn6KoHbuAVqDMaIQ

So far I'm managed to get it to read the contents of a pair of parenthesis, in which we type the amount of hours by calculating it ourselves.
I'd like to hopefully skip this step, so as soon as we type in the rostered start & finish time, it calculates the difference and adds it to the hours calculated at the right and bottom of the sheet.
I know this could be attained by splitting the start & finish into seperate cells. but I'd prefer to keep the format, as it is in keeping with the style of the other departments.
I would be happy to change the "Close" time to 2300, but if it's possible to make a case to convert that, it would be terrific.

Any help would be greatly appreciated.

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? Many members are unable or unwilling to access file-sharing sites.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[infadibulum]

Hi there, thanks for the advice, attached is the roster, with the Highlighted line in yellow being what I'd like to attain, having manually entered the information.

[FDibbins]

This would be a WHOLE lot simpler if you used 2 columns for each day, and just typed in the actual start and end times in each column. That way you can calc the hours using the built-in time functions in excel

[JohnTopley]

Try

in K3

=IF(B3="RDO",0,(TIMEVALUE((MID(B3,8,2)&":"&MID(B3,10,2)))-TIMEVALUE((MID(B3,1,2)&":"&MID(B3,3,2))))*24)

I am assuming "Close" become 2300 (and why not).

BUT as previously stated having start/finish columns is much easier and more sensible.