Weekend Order Date Add Business Days for Due Date

[turtlepatch]

I need to determine the due date of an order. Orders come in between 18:00 of one day until 17:59 of the next day. Anything between 18:00 and 24:00 counts the next day as day 0. After 24:00 the current day is day zero. I need to show the due date (day 2). I have a formula from Ken Hamady for adding business days to a date. My problem occurs when orders come in on the weekend. Any order coming in after 18:00 on Friday, has day 0 as the next Monday. The calculation works correctly until I have an order come in on Saturday. It counts Saturday as day zero and then adds 2 business days to it and has it due on Tuesday instead of Wednesday. Here is the code I am using:

@time is the order time.
@time2 is 18:00:00

This is the code currently for my due date:

if {@time}< {@time2}
then
(WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:={cd_sfly_order.Created-Date}; // Put your field name in here
NumberVar Add:= 2; // put the number of days here to add (a positive number)
NumberVar Added := 0;

WHILE Added < Add
Do (target := target +1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added+1
else Added:=Added);
Target)

else
(WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:={cd_sfly_order.Created-Date}; // Put your field name in here
NumberVar Add:= 3; // put the number of days here to add (a positive number)
NumberVar Added := 0;

WHILE Added < Add
Do (target := target +1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added+1
else Added:=Added);
Target)


Any help would be greatly appreciated.

 

[turtlepatch]

Just wondering if anybody had any ideas on this?
Thanks
Ann

 

[lbass]

For one thing, if you expect orders on Saturday, then:

if dayofweek (target) in 2 to 7 and not (target in holidays)

-LB

 

[turtlepatch]

Sorry, I forgot to include my @holiday formula.

//Holiday Array Formula for the report Header:
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2010,11,25), // you can put in as many lines for holidays as you want.
Date (2010,12,24),
Date (2010,12,31)
];
0

 

[lbass]

My point was that the dayofweek argument should be changed to 7 (from 6), although other changes to the formula might be required, too. Not sure without further effort.

-LB

 

[turtlepatch]

Orders can actually come in any day of the week, Monday through Sunday. Day zero starts on the first "Business day" and the formula I have counts Sunday as day zero if the order comes in on Saturday. It actually needs to have Monday be day zero.

 

[lbass]

I am unclear on what you are considering a "Business Day" and what exactly the problem is. What should happen if an order comes in on a Friday, Saturday, or Sunday?

-LB

 

[turtlepatch]

A "Business Day" would be Monday through Friday. Those days are the days that "count". If an order comes in on Friday and it is before 18:00 then Friday is day zero. If it comes in after 18:00 then Monday is day zero. If an order comes in any day over the weekend, Monday is day zero. The problem is with the formula above, if an order comes in on Friday, it's correct because it follows the rule of not counting weekends. But if it comes in on Saturday, which is a weekend day, it counts Sunday as day zero and kind of ignores the weekend rule.

 

[lbass]

Are the following the correct target days for the following scenarios:
Day Zero Target

Thursday, 4pm Thursday Monday
Thursday, 8pm Friday Monday
Friday, 4pm Friday Monday
Friday, 8pm Monday Wednesday
Saturday, 4pm Monday Wednesday
Saturday, 8pm Monday Wednesday
Sunday, 4pm Monday Wednesday
Sunday, 8pm Monday Wednesday

???

-LB

 

[turtlepatch]

It is always 2 "Business Days", Monday through Friday, from Day Zero for the Target Date. So the two below that have Friday as Day Zero, Day One would be Monday and Day Two would be Tuesday, the Target Date.

Day Zero Target

Thursday, 4pm Thursday Monday
Thursday, 8pm Friday Monday Tuesday
Friday, 4pm Friday Monday Tuesday
Friday, 8pm Monday Wednesday
Saturday, 4pm Monday Wednesday
Saturday, 8pm Monday Wednesday
Sunday, 4pm Monday Wednesday
Sunday, 8pm Monday Wednesday