Automating Time Values on a Work Schedule (Rota)

Hi,

I'm trying to create a spreadsheet to run the rotas at work so that we don't constantly have to recalculate all the hours when we make a change. I'm trying to get the rota to understand that if I put 10- it is worth 13 hours so that at the end of the week it can add up the different values to give a weekly hours total.

The other values I need it to understand are (-10:30 means 3.5 hours) (A/L means 8hours) (B/H means 8hours) (x means 0) (M/L means 0) (9-5 means 8)

If anyone knows a way to do this it would be massively appreciated, I'm struggling with the fact the shifts are split across two days as its a children's home and we work over night.

I have tried to attach an image showing what I'm aiming for.

Welcome to the forum.

Your thread title is poor, so I shall update it for you as you are new, but please read our rules on thread titles.

There are instructions at the top of the page explaining how to attach your sample workbook.

Which version of Excel are you using? Please update your forum profile.

Here is a possibility.
1. Produce a table with the inputs in one column such as S and associated hours in another such as T.
2. Populate the totals column using: =SUMPRODUCT((S$4:S$10=A2:G2)*(T$4:T$10))
Let us know if you have any questions.