Hi there, i wonder if anyone can help me. I have a sheet with Monday - Sunday as column headings from B - H. The heading of column A is Name. I am creating a rota for my team and need to add a total amount of each team members hours to column H depending on what hours are in each cell next to their name.



The issue is that the start and finish times are in the same cell e.g 11-22:30 in cell B1, 17:00-21:00 in C1, 19:00-00:00 in D1. Based on this is there a formula i can use in E1 for example that the total number of hours for this line is 20.5 hours

This way i can easily work out if full and part timers are getting enough or too many hours when i am creating the rota and adding shifts

Hope this makes sense and that someone can help.

Regards

Neil

Supposing all time digits are in the hh:mm format ( 5 characters) the following seems to work
=SUMPRODUCT(-(RIGHT(A1:B1,5)))-SUMPRODUCT(-(LEFT(A1:B1,5)))
adapt ranges as needed and format the result cell as needed also ( if you have more than 24 hrs remember to use [h]:mm)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post