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

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi, I have attached a stripped down file which hopefully demonstrates what I can trying achieve. On the agency tab is where I would like to select the name in cell B7 which could then potentially return the correct number of shifts in the table from column V to AI on a week number basis.


Many thanks,
Gazsim

Hi Bo,

Many thanks for this, much appreciated.

Just one further question - how can I account for weeks that cross two months?

Thanks,
Gazsim

Hi Bo, the sheets for the other months are exactly the same only for each individual month. I have tried to use the below simply to add up each COUNTIFS but the only trouble is it returns an error when there is no data contained on that sheet which there won't be until later in the year.

=IFERROR(COUNTIFS(INDEX(January!$E$9:$AI$100,MATCH($B$7,January!$B$9:$B$100,),),Z$14,January!$E$8:$AI$8,">="&$F19,January!$E$8:$AI$8,"<="&$G19),"")+IFERROR(COUNTIFS(INDEX(February!$E$9:$AI$100,MATCH($B$7,February!$B$9:$B$100,),),Z$14,February!$E$8:$AI$8,">="&$F19,February!$E$8:$AI$8,"<="&$G19),"")


Thanks!

Brilliant! Perfect! What a formula though, I could have never have done that!!! :-)

Thanks again,
Gazsim