Hello, I work in the construction industry and I've created a schedule that tracks 8 separate task/deadlines for multiple projects (the task are the same for each project but the projects have different start dates.) The timeframe for each task will always remain the same with event A happening 1 day after the project start date, event B happening 3 days after project start and 2 days after event A, etc.
I started with an if/then formula but have since changed to a vlookup (=IFNA(VLOOKUP((I$5-$C18),Inputs!$A$1:$B$12,2,FALSE)," "). I would like to roll any task that occurs on a Saturday or Sunday to Monday and shift the remaining task one day. I can resolve some of my issue with a formula but realize a macro is probably more suited. Also, I would like to have input for skipping a day as needed (ie. holidays) and rolling to the next workday. I am currently using Workday.INTL to show only weekdays and manually manipulating the file to skip weekends. I do have some VBA experience but I have been far removed from it for over 10 years so I don't have the skill set anymore to write the macro. Thanks for any help.
Also, I originally started with an if formula to hide the task on weekends and holidays =IF(OR(H$4="Saturday",H$4="Sunday",H$5=$B$2,H$5=$B$3)," ",IFNA(VLOOKUP(H$5-$C27,Inputs!$A$1:$B$12,2,FALSE)," "))
I started with an if/then formula but have since changed to a vlookup (=IFNA(VLOOKUP((I$5-$C18),Inputs!$A$1:$B$12,2,FALSE)," "). I would like to roll any task that occurs on a Saturday or Sunday to Monday and shift the remaining task one day. I can resolve some of my issue with a formula but realize a macro is probably more suited. Also, I would like to have input for skipping a day as needed (ie. holidays) and rolling to the next workday. I am currently using Workday.INTL to show only weekdays and manually manipulating the file to skip weekends. I do have some VBA experience but I have been far removed from it for over 10 years so I don't have the skill set anymore to write the macro. Thanks for any help.
| Release Date | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | |
| 1/10/2022 | 1/11/2022 | 1/12/2022 | 1/13/2022 | 1/14/2022 | 1/15/2022 | 1/16/2022 | 1/17/2022 | 1/18/2022 | 1/19/2022 | 1/20/2022 | 1/21/2022 | 1/22/2022 | ||
1/9/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | |||||||
1/10/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | |||||||
1/11/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | |||||||
1/12/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | |||||||
1/13/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | ||||||||
1/14/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | |||||||||
1/16/2022 | Footer/FINS | Forms | Plumb Slab | PINS | ||||||||||
1/17/2022 | Footer/FINS | Forms | Plumb Slab |
| Example.xlsx | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| C | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | ||||||
| 4 | Release Date | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | ||||||
| 5 | 1/13/2022 | 1/14/2022 | 1/15/2022 | 1/16/2022 | 1/17/2022 | 1/18/2022 | 1/19/2022 | 1/20/2022 | 1/21/2022 | 1/22/2022 | 1/23/2022 | 1/24/2022 | 1/25/2022 | |||||||
| 6 | 1/9/2022 | Plumb Slab | PINS | Cover | SINS | Pour Slab | ||||||||||||||
| 7 | 1/10/2022 | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | |||||||||||||
| 8 | 1/11/2022 | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | |||||||||||||
| 9 | 1/12/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | ||||||||||||
| 10 | 1/13/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | ||||||||||||
| 11 | 1/14/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | Pour Slab | ||||||||||||
| 12 | 1/16/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | SINS | |||||||||||||
| 13 | 1/17/2022 | Footer/FINS | Forms | Plumb Slab | PINS | Cover | ||||||||||||||
| 14 | 1/19/2022 | Footer/FINS | Forms | Plumb Slab | PINS | |||||||||||||||
Working Sheet | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H4 | H4 | =TEXT(WEEKDAY($B$1,1),"dddd") |
| I4 | I4 | =TEXT(WEEKDAY($B$1+1,1),"dddd") |
| J4 | J4 | =TEXT(WEEKDAY($B$1+2,1),"dddd") |
| K4 | K4 | =TEXT(WEEKDAY($B$1+3,1),"dddd") |
| L4 | L4 | =TEXT(WEEKDAY($B$1+4,1),"dddd") |
| M4 | M4 | =TEXT(WEEKDAY($B$1+5,1),"dddd") |
| N4 | N4 | =TEXT(WEEKDAY($B$1+6,1),"dddd") |
| O4 | O4 | =TEXT(WEEKDAY($B$1+7,1),"dddd") |
| P4 | P4 | =TEXT(WEEKDAY($B$1+8,1),"dddd") |
| Q4 | Q4 | =TEXT(WEEKDAY($B$1+9,1),"dddd") |
| R4 | R4 | =TEXT(WEEKDAY($B$1+10,1),"dddd") |
| S4 | S4 | =TEXT(WEEKDAY($B$1+11,1),"dddd") |
| T4 | T4 | =TEXT(WEEKDAY($B$1+12,1),"dddd") |
| H5 | H5 | =B1 |
| I5:T5 | I5 | =H5+1 |
| H6:T14 | H6 | =IFNA(VLOOKUP(H$5-$C6,Inputs!$A$1:$B$12,2,FALSE)," ") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| H5:AL5 | Cell Value | =$B$2 | text | NO |
| H5:AL5 | Cell Value | =$B$3 | text | NO |
| H6:AL14 | Cell Value | contains "PINS" | text | NO |
| H6:AL14 | Cell Value | contains "SINS" | text | NO |
| H6:AL14 | Cell Value | contains "Footer" | text | NO |
| H6:AL14 | Cell Value | contains "Pour Foot" | text | NO |
| H6:AL14 | Cell Value | contains "Flatwork" | text | NO |
| H6:AL14 | Cell Value | contains "Pour Slab" | text | NO |
Also, I originally started with an if formula to hide the task on weekends and holidays =IF(OR(H$4="Saturday",H$4="Sunday",H$5=$B$2,H$5=$B$3)," ",IFNA(VLOOKUP(H$5-$C27,Inputs!$A$1:$B$12,2,FALSE)," "))
