Roster Days to Hours per Week?

Hi All,

I am currently working on a 2019 Roster for our warehouse department but have a problem in getting a formula to calculate back what days any agency workers are working into hours on a week by week basis if you follow. The roster naturally has a list of names in a column and then a column for each day of the month, 1, 2, 3 etc. Above this is each day of the week and then covering each week is the week number so for example Jan 1 to Jan 6 has week 1 in the row above as a merged cell. Then basically against each name / date is an indication of what shift they are working - be it D(day) or N(night).

From this I need to create a seperate table listing each week number and then a formula that will look up the name from a seperate cell, match this with the first table and then look for each D or N in a given week (1,2,3 etc) and then sum these so I could end up with John Smith showing as working 4 night shifts in week 1 and 4 day shifts in week 2.


The ultimate goal is for me then to convert that number 4 back into hours (so for example 4*12=48) which would show how many hours they are working each week.


Many thanks for any input you can offer,

Gazsim