SOLVED

struggle with WORKDAY function

Occasional Contributor

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
best response confirmed by Brian_Paasch (Occasional Contributor)
Solution

Re: struggle with WORKDAY function

Try

=WORKDAY([@[order system date]-1,1,[Holidays date list])

Re: struggle with WORKDAY function

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!

Re: struggle with WORKDAY function

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

Re: struggle with WORKDAY function

Ohhhh! Yes, makes sense! Thank you!