Having difficulty with live 'Week Commencing' calculations and lookups
I've posted a related question here before, and although I got a response, it didn't achieve what I was looking for - therefore, I've changed my approach.
Essentially, what Iβm trying to produce is a live view of all new starts, transfers and leavers to be viewed by various stakeholders once they are officially confirmed. However, this will be on a weekly basis (to replace a manually put together weekly email that goes out on Fridays). My current set-up is 3 separate sheets (for the 3 different staff movement types), all with Reports that feed into an overall Dashboard. I would also use this report to COUNTIF the number of appropriate staff on each sheet and showcase this metric on the Dashboard also (see screenshot)
This view should refresh every Friday showcasing next weekβs list, and should be visible from Friday to Thursday, automatically refreshing every Friday. My way to do this at the moment is a helper column called βCurrent Viewβ whereby I filter the Report to show only relevant cases. Iβm not sure if Iβm overcomplicating this and this is where Iβm going wrong but my thinking is to basically calculate this as (using a New start as an example):
- Each new staff member has a βStart Dateβ input as a new row via a Form
- βStart Dateβ > informs a βWeek Commencingβ column for the Monday of that week
- βWeek Commencingβ column informs a βWeek Commencing (+4)β column for the Friday I would like the automatic refresh to take place on
- A βCurrent Viewβ column to highlight (currently via an IFERROR and COUNTIF) which rows are to appear on the current weekly list.
Iβm almost finished and just basically need help with parts 3 and 4. Iβve tested a few formulas in the βCurrent Viewβ column and currently settled on:
- =IFERROR(COUNTIFS([Week Commencing (+4)]@row :[Week Commencing (+4)]@row , >=TODAY(), [Week Commencing (+4)]@row :[Week Commencing (+4)]@row , <=TODAY(5)), "")
The crux of my issue is that Iβm not confident in this formula holding up as Iβve had to try multiple and several have appeared to work initially only not to update or encounter some other error. I thought I had cracked this last week but when I went to check something before, it hadnβt updated properly and the wrong staff were showing. Iβve made some tweaks but hoping some wonderful person in these forums has experience and know-how to definitively solve this. Can anyone help? Or show me where Iβm going wrong with the set-up?
Best Answer
-
Try putting this in a checkbox column to check the box for any entry date that is between the next upcoming Friday and the following Thursday:
=IF(AND([Date Column Name]@row >= TODAY() + (6 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 6, 7, 0), [Date Column Name]@row < TODAY() + (6 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 6, 7, 0) + 7), 1)
Answers
-
Try putting this in a checkbox column to check the box for any entry date that is between the next upcoming Friday and the following Thursday:
=IF(AND([Date Column Name]@row >= TODAY() + (6 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 6, 7, 0), [Date Column Name]@row < TODAY() + (6 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 6, 7, 0) + 7), 1)
-
Thank you - this worked!
