I have a roster with shifts represented by letters e.g N-night, D-day, E-evening. I would like to add a formula at the bottom of a fortnight to calculate the hours based on N=11 hours, D=8 hours, E=8 hours.
I have used the formula below to add up the hours but it becomes a very long formula to cover each shift for each cell
IF(S4="D",8)+IF(S4="N",11)+IF(S4="E",8)+IF(S5="D",8)+IF(S5="N",11)+IF(S5="E",8)+ etc.... until I have covered the fortnight range of cells i.e 14 cells
Are there any shortcuts? I tried to make it a range but it did not work e.g IF(S4:18="E",8)+(S4:S18="N",11) + etc...
Bookmarks