Spreadsheet: 749383d1632765515-if-function-problem-or-maybe-an-alternative-way-is-better-for-a-schedule-gloriousglenn
Sheet: Schedule

 |  | H1 | P1 | P2 | P3 | P4 | P5
2021-09-13 00:00:00 |  |  |  |  |  |  |  |  | 1
2021-09-14 00:00:00 |  |  |  |  |  |  |  |  | 2 | I have two problems!  1.  B1 is the start of a 7 day cycle, but weekends don't count in the cycle!  So, B2 to [NAME] is easy enough (function already added), but how do I show that if the cell in column A is a weekend then the cell in column B should be blank but then continue from the Monday?  So [NAME] is "5", but [NAME] and [NAME] should be blank, B9 should then be "6", B10 should be "7", B11 should then be back to "1", etc.   Then....  2.  What if Mon 4-Oct becomes a day off.  I would like it that if I delete the function from cell B23, the cells in B24 and beyond automatically change to the correct sequence.  So B24 would then change from 2 to 1, B25 from 3 to 2, etc.  So it would be like saying "If the previous cell is blank, have a look at the cell before that and +1 to that number, but if that's blank too go back to the cell before that! |  | 2021-10-01 00:00:00
2021-09-15 00:00:00 |  |  |  |  |  |  |  |  | 3 |  |  | 2021-10-04 00:00:00
2021-09-16 00:00:00 |  |  |  |  |  |  |  |  | 4
2021-09-17 00:00:00 |  |  |  |  |  |  |  |  | 5
2021-09-18 00:00:00
2021-09-19 00:00:00
2021-09-20 00:00:00 |  |  |  |  |  |  |  |  | 6
2021-09-21 00:00:00 |  |  |  |  |  |  |  |  | 7
2021-09-22 00:00:00 |  |  |  |  |  |  |  |  | 1
2021-09-23 00:00:00 |  |  |  |  |  |  |  |  | 2
2021-09-24 00:00:00 |  |  |  |  |  |  |  |  | 3
2021-09-25 00:00:00
2021-09-26 00:00:00
2021-09-27 00:00:00 |  |  |  |  |  |  |  |  | 4
2021-09-28 00:00:00 |  |  |  |  |  |  |  |  | 5
2021-09-29 00:00:00 |  |  |  |  |  |  |  |  | 6
2021-09-30 00:00:00 |  |  |  |  |  |  |  |  | 7
2021-10-01 00:00:00
2021-10-02 00:00:00
2021-10-03 00:00:00
2021-10-04 00:00:00
2021-10-05 00:00:00 |  |  |  |  |  |  |  |  | 1
2021-10-06 00:00:00 |  |  |  |  |  |  |  |  | 2
2021-10-07 00:00:00 |  |  |  |  |  |  |  |  |  | Column J is just to show what "should" be in column B!
2021-10-08 00:00:00
2021-10-09 00:00:00 |  |  |  |  |  |  |  |  |  | Today's date is yellow through conditional formatting
2021-10-10 00:00:00
2021-10-11 00:00:00
2021-10-12 00:00:00
2021-10-13 00:00:00
2021-10-14 00:00:00
2021-10-15 00:00:00
2021-10-16 00:00:00
2021-10-17 00:00:00
2021-10-18 00:00:00
2021-10-19 00:00:00
2021-10-20 00:00:00
2021-10-21 00:00:00
2021-10-22 00:00:00
2021-10-23 00:00:00
2021-10-24 00:00:00
2021-10-25 00:00:00
2021-10-26 00:00:00
2021-10-27 00:00:00
2021-10-28 00:00:00
2021-10-29 00:00:00
2021-10-30 00:00:00
2021-10-31 00:00:00
2021-11-01 00:00:00
2021-11-02 00:00:00
2021-11-03 00:00:00
2021-11-04 00:00:00
2021-11-05 00:00:00
2021-11-06 00:00:00
2021-11-07 00:00:00
2021-11-08 00:00:00
2021-11-09 00:00:00
2021-11-10 00:00:00
2021-11-11 00:00:00
2021-11-12 00:00:00
2021-11-13 00:00:00
2021-11-14 00:00:00
2021-11-15 00:00:00
2021-11-16 00:00:00
2021-11-17 00:00:00
2021-11-18 00:00:00
2021-11-19 00:00:00
2021-11-20 00:00:00
2021-11-21 00:00:00
2021-11-22 00:00:00
2021-11-23 00:00:00
2021-11-24 00:00:00
2021-11-25 00:00:00
2021-11-26 00:00:00
2021-11-27 00:00:00
2021-11-28 00:00:00
2021-11-29 00:00:00
2021-11-30 00:00:00
2021-12-01 00:00:00
2021-12-02 00:00:00
2021-12-03 00:00:00
2021-12-04 00:00:00
2021-12-05 00:00:00
2021-12-06 00:00:00
2021-12-07 00:00:00
2021-12-08 00:00:00
2021-12-09 00:00:00
2021-12-10 00:00:00
2021-12-11 00:00:00
2021-12-12 00:00:00
2021-12-13 00:00:00
2021-12-14 00:00:00
2021-12-15 00:00:00
2021-12-16 00:00:00
2021-12-17 00:00:00
2021-12-18 00:00:00
2021-12-19 00:00:00
2021-12-20 00:00:00
2021-12-21 00:00:00
2021-12-22 00:00:00
2021-12-23 00:00:00
2021-12-24 00:00:00
2021-12-25 00:00:00
2021-12-26 00:00:00