Hi,
I have a formula which calculates the number of unbooked weeks between a specific timeframe. It works well, however, each week I need to update the formulas, so it looks at the correct weeks when calculating.
Row 1: weekly dates
Rows 2 onwards: each person's unbooked weeks
Current Formula is: =COUNTIFS(E2:Z2,"",$E$1:$Z$1,">="&C2,$E$1:$Z$1,"<="&IF($B2="Leaver",$D2,Z$1))
However, every Monday I need to go in and change column "E" to "F", then the Monday after that to change "F" to G" to make sure it points to the correct weeks (the end date will always remain the same). Essentially the formula needs to calculate unbooked weeks from the current week to the end -- anything that occurred in the past is irrelevant.
I was thinking of nesting =TODAY()-WEEKDAY(TODAY(),3) somehow in there, but am getting caught-up on the syntax.
Any ideas would be very helpful! Thanks!
Bookmarks