SOLVED

struggle with WORKDAY function

Occasional Contributor

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

@Brian_Paasch 

Try

 

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

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!

@Brian_Paasch 

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
Ohhhh! Yes, makes sense! Thank you!