Hi there,
Looking to have cell M3 = always equal the closest Monday. Right now my formula works for weekend conditions but not if user inputs run data of say 10-4-22. Any help would be appreciated:
Looking to have cell M3 = always equal the closest Monday. Right now my formula works for weekend conditions but not if user inputs run data of say 10-4-22. Any help would be appreciated:
| SRI Schedule Prototype.xlsx | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
| 1 | Schedule Run Date: | 10/3/2022 | 1 | Duration | Contract | Submittals | Labor Req. | Manpower | 32 | 32 | 32 | 32 | 32 | ||||||
| 2 | Oct | ||||||||||||||||||
| 3 | Job # | Job Name | Roof System | PM | Supt | Start Date | End Date | Contractor | 3 | 4 | 5 | 6 | 7 | ||||||
Schedule | |||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G1 | G1 | =WEEKDAY(C1,2) |
| M1:Q1 | M1 | =IF(SUM(M4:M27)=0,"",SUM(M4:M27)) |
| M2 | M2 | =CHOOSE(MONTH(O3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") |
| M3 | M3 | =IF(WEEKDAY(C1,2)>=6,WORKDAY(C1,1),WORKDAY(C1,0)) |
| N3:Q3 | N3 | =WORKDAY(M3,1) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| M3:ADL27 | Expression | =COUNTIFS(Holidays!$F$3:$F$68,M$3) | text | NO |
| I:I | Expression | =AND(NOT(ISBLANK($A1)),$I1="N") | text | NO |
| I:I | Expression | =AND(NOT(ISBLANK($A1)),$I1="Y") | text | NO |
| J:J | Expression | =AND(NOT(ISBLANK($A1)),$J1="S") | text | NO |
| J:J | Expression | =AND(NOT(ISBLANK($A1)),$J1="NS") | text | NO |
| J:J | Expression | =AND(NOT(ISBLANK($A1)),$J1="A") | text | NO |
