I am trying to come up with a way to calculate the hours on Excel automatically for my rota. The issue I’m running into is that we do not list “off times” on the schedule, so the shifts are input as “12-cls” or “5-LB”.
I am trying to find a way to assign the number 10 to “5-LB” or 8 to “holiday” for example, but I am having a hard time finding the right formula for it. Currently I just have the shift and it’s prospective hours that I manually click and add using =Sum(). It would be awesome if I could find a formula that would let me just highlight a someone’s schedule and automatically add up the hours. I’ve attached an example of what our rota looks like. Help! example of rota
