How to calculate hours worked using two times (am/pm) in one cell

[ilginone]

Hello

I have a work rota, and would like to calculate the hours worked from a single cell.

e.g.
CELL A2 Employee 1
CELL B1 Monday
CELL B2 10am-2.30pm.

I would expect the above example to return 4.5 in a cell called Hours

I would then like all the days to be added to give a weekly total per employee.

Most solutions I have seen online have a check in/out procedure, but since I want to print the rota and not have it look like a series of numbers, I was hoping for a more pleasing solution.

Thanks!

[NoSparks]

To get a working solution for this you will need to post a sample worksheet.

What you're looking for is not that difficult but the code will be very dependent upon the consistency of how the work times have been entered and being able to address every possible variation.

Is 2.30pm an actual recognized time in your Excel? It isn't in mine.

[ilginone]

Thank you for offering a solution.

Excel accepts am/pm as a text cell. I downloaded a free template, and it calculated am/pm hours worked, but I believe a macro(s) is involved, which is password protected.

I have uploaded my sample of the data I need looked at.

Thanks!

[NoSparks]

Hopefully you can make use of this.
I altered the layout of your sheet a little to be easier to work with.
This uses the Worksheet_Change event to calculate the hours worked each shift as they are filled in.

Good luck with your project.

[ilginone]

This is exactly what I was looking for!

However, would you be able to alter it so that i would not need a daily hours summary, just end of week?

Thank you so much for your help!

[NoSparks]

Would it be too easy to just hide those columns ?

[ilginone]

I didn't think of that!

Thank you very much for this!