Forum Discussion
struggle with WORKDAY function
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?
4 Replies
- Yes, that works perfectly! Although I am struggling to wrap my brain around WHY that works, seems like an system date of Sat would be interpreted as a Fri.... Not understanding, but yes, it works!! Thank you!
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
