Dec 28 2021 06:01 AM
Greetings all, I’m thinking this is an easy fix, but I’m not winning. When an order hits our system it gets a system datetime stamp. To calculate (Excel, 365) our service date, I need to adjust the order date to the first available WORKDAY. If the order comes in on a Sat or Sun, the order date needs to adjust to the following Monday. Same applies to holidays (I have a table of our company holidays.) However, if the order arrives on a WORKDAY, it needs to stay there and not move.
This seemed simple, I did:
Adjusted Order Date = WORKDAY([@[order system date],1,[Holidays date list])
That works fine IF the order arrives on a weekend or holiday. Even this past Thanksgiving, if an order arrived on Thursday, Nov 25, the function moved the adjusted order date all the way to Mon, Nov 29. That is perfect.
BUT, if an order arrives on a workday, it should not move. But it does, it increments by one day. And if it arrives on a Fri, it jumps to the following Mon. Not what I need!
I tried changing the 1 to a zero, but then the date never moves, no matter what the system date is.
Ideas?
Dec 28 2021 06:08 AM
SolutionDec 28 2021 06:33 AM
Dec 28 2021 06:42 AM
The idea is to go back one day, and then find the first working day after that.
If you start with a working day, you'll end up on that day again.
But if you start on Saturday, you go to Friday, and the first working day after that is Monday.
And if you start on Sunday, you go to Saturday, and the first working day after that is Monday.
Order system date | One day before | Next working date |
Monday | Sunday | Monday |
Tuesday | Monday | Tuesday |
Wednesday | Tuesday | Wednesday |
Thursday | Wednesday | Thursday |
Friday | Thursday | Friday |
Saturday | Friday | Monday |
Sunday | Saturday | Monday |
Dec 28 2021 06:08 AM
SolutionTry
=WORKDAY([@[order system date]-1,1,[Holidays date list])