JiveTurkey2123
New Member
- Joined
- Jan 25, 2022
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
Hi all,
Hope all is well! I wanted to ask for a huge favor here with the workday formula.The first part of the IF is correct where E5=“CA”, however i need to fix the part where E5 does not equal CA (this part: IF(AND(AM5<>"N/A",AM5<>"??",AM5<>""),AM5-4,""))
=IF(E5="CA",(IF(AND(AM5<>"N/A",AM5<>"??",AM5<>""),WORKDAY(WORKDAY.INTL(WORKDAY(AM5,-3,Production!$N$108:$N$119),-3,11,Production!$N$108:$N$119)+1,-1,Production!$N$108:$N$119),"")),IF(AND(AM5<>"N/A",AM5<>"??",AM5<>""),AM5-4,""))
AM5 is start date
Production!$N$108:$N$119 is the holiday list
This part is working:
When E5=“CA”
This formula that will calculate 6 days in the past from the start date.
Sunday's cannot be included in the 6 day "count".
Monday-Saturday AND Holidays CAN be included in the 6 day "count".
This part is not working:
When E5 does NOT equal “CA”
This formula that will calculate 3 days in the past from the start date.
Sunday's cannot be included in the 3 day "count".
Monday-Saturday AND Holidays CAN be included in the 3 day "count".
However, out of the 3 total count days:
Day 0: cannot count Saturday, Sunday or Holiday's
Day 1-2: cannot count Sunday or Holiday's
Day 3: cannot count Saturday, Sunday or Holiday's
The final result start date can only land on a Monday-Friday and not on a holiday, Saturday or Sunday
Example:
If start date is 9/3/2024, should return 8/29/2024
Day 0: 8/29 (return date)
Day 1: 8/30
Day 2: 8/31
Day 3: 9/3 (start date)
9/1 doesnt count since its a Sunday
9/2 doesnt count since its a Holiday (Labor Day)
Example 2:
If start date is 9/16/2024, should return 9/12/2024
Day 0: 9/12 (return date)
Day 1: 9/13
Day 2: 9/14
Day 3: 9/16 (start date)
9/15 doesnt count since its a Sunday
Your help would be greatly appreciated!!
Hope all is well! I wanted to ask for a huge favor here with the workday formula.The first part of the IF is correct where E5=“CA”, however i need to fix the part where E5 does not equal CA (this part: IF(AND(AM5<>"N/A",AM5<>"??",AM5<>""),AM5-4,""))
=IF(E5="CA",(IF(AND(AM5<>"N/A",AM5<>"??",AM5<>""),WORKDAY(WORKDAY.INTL(WORKDAY(AM5,-3,Production!$N$108:$N$119),-3,11,Production!$N$108:$N$119)+1,-1,Production!$N$108:$N$119),"")),IF(AND(AM5<>"N/A",AM5<>"??",AM5<>""),AM5-4,""))
AM5 is start date
Production!$N$108:$N$119 is the holiday list
This part is working:
When E5=“CA”
This formula that will calculate 6 days in the past from the start date.
Sunday's cannot be included in the 6 day "count".
Monday-Saturday AND Holidays CAN be included in the 6 day "count".
This part is not working:
When E5 does NOT equal “CA”
This formula that will calculate 3 days in the past from the start date.
Sunday's cannot be included in the 3 day "count".
Monday-Saturday AND Holidays CAN be included in the 3 day "count".
However, out of the 3 total count days:
Day 0: cannot count Saturday, Sunday or Holiday's
Day 1-2: cannot count Sunday or Holiday's
Day 3: cannot count Saturday, Sunday or Holiday's
The final result start date can only land on a Monday-Friday and not on a holiday, Saturday or Sunday
Example:
If start date is 9/3/2024, should return 8/29/2024
Day 0: 8/29 (return date)
Day 1: 8/30
Day 2: 8/31
Day 3: 9/3 (start date)
9/1 doesnt count since its a Sunday
9/2 doesnt count since its a Holiday (Labor Day)
Example 2:
If start date is 9/16/2024, should return 9/12/2024
Day 0: 9/12 (return date)
Day 1: 9/13
Day 2: 9/14
Day 3: 9/16 (start date)
9/15 doesnt count since its a Sunday
Your help would be greatly appreciated!!
