Calculate Date to Skip Weekend

[Rich S]

Scenario: When users insert an order Due Date, it must be at least three days from the current date (Get (CurrentDate) + 3 ) --or be in the future beyond three days from the current date. However, we want it where if the order is set for a Wednesday or Thursday, the fulfillment date would be pushed to the following Monday since nobody's here on weekends.

I found this calculation from our fearless site leader, Comment, from 2005:

Let (

dueDate = OrderDate + 7

;

dueDate + Position ( "17" ; DayOfWeek ( dueDate ) ; 1 ; 1 )

)

...and changed OrderDate + 7 to OrderDate +3, but try as I might I can't get the calculation to work where a new date is calculated (that's on a Monday.) What am I missing here?

 

TIA for your help,

Rich

 

 

Edited March 25, 2022 by WF7A
grammar police

[comment]

21 minutes ago, WF7A said:

I can't get the calculation to work where a new date is calculated (that's on a Monday.)

I am not sure what exactly that means. What is your input (OrderDate), what result do you get, and how does it differ from the result you expect?

 

Edited March 25, 2022 by comment

[Rich S]

*heavy sigh* Stupidity on my part--I set the calculation as a number instead of a date. Here's the test file (that works.)

Sorry to have wasted your time.

Date Calc.fmp12

[comment]

21 minutes ago, WF7A said:

I set the calculation as a number instead of a date.

We've all done that at some point... In time you learn to recognize an unexpected 6-digit number that begins with 7 as the serial number representation of a date.

 

[Rich S]

Thanks for the pat on the shoulder; I just hate wasting your (and other people's) time...especially when it's gratis. (As an aside, I'd like to offer some kind of token 'thank you' to you for all the help you've given me over the years; if there's something you'd like (that I can afford--no yachts or houses), send me a PM and I'll see if I can make it happen.)

I modified the file so it includes timestamps, but I'm stuck on the calculation, DateCalcUsingTeimstamp_lcm--I can't get the time part of the timestamp to mirror the other timestamps'. I tried a few Substitute and GetAs... calcs, but no luck.

Push Date Past Weekend Calc.fmp12

[comment]

If you want both the input and the output of the calculation to be timestamps, then you could do:

Let (
dueDate = GetAsDate ( OrderTimestamp ) + 3
;
Timestamp (
dueDate + Position ( "17" ; DayOfWeek ( dueDate ) ; 1 ; 1 ) ;
GetAsTime ( OrderTimestamp ) 
)
)

or, if you prefer:

Let (
dueTS = OrderTimestamp + 259200
;
dueTS  + 86400 * Position ( "17" ; DayOfWeek ( dueTS ) ; 1 ; 1 )
)

where OrderTimestamp is the input field.

Hopefully the logic of both is self-explanatory.

 

Edited April 6, 2022 by comment