Tanner I think the code is almost there but because the example order I am checking against has 9 delivery days its running across 2 weekends so its adding 2 more days to calc but needs to add 4
@carlawhite Yes it appears that it is a bit more complicated. I was just presenting this as an example as I’ve never had reason to do this. I will take another look at it
@carlawhite How about:
DATEADD(DAY,((1+OrderHed.DeliveryDays_c/7)*2)+OrderHed.DeliveryDays_c,ShipHead.ShipDate)@carlawhite I just noticed that could land on a weekend. Try this one instead:
DATEADD(DAY,(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,((1+OrderHed.DeliveryDays_c/7)*2)+OrderHed.DeliveryDays_c,ShipHead.ShipDate))=1 THEN 1
WHEN DATEPART(WEEKDAY,DATEADD(DAY,((1+OrderHed.DeliveryDays_c/7)*2)+OrderHed.DeliveryDays_c,ShipHead.ShipDate))=7 THEN 2
ELSE 0 END),DATEADD(DAY,((1+OrderHed.DeliveryDays_c/7)*2)+OrderHed.DeliveryDays_c,ShipHead.ShipDate))
For future reference, this code does not work accurately 
I’m assuming there is a way to do this in an equation but it would take much longer than I have to devote
@carlawhite Did you try this ?
Ive done an example with OrderRel.
To get the date that the shipment will fall based on your could use the following and get it to add days based on the datepart.
case
when datepart(weekday,(dateadd(day,OffsetDays,OrderRel.ReqDate))) = 1 then dateadd(day,OffsetDays + 1,OrderRel.ReqDate)
when datepart(weekday,(dateadd(day,OffsetDays,OrderRel.ReqDate))) = 7 then dateadd(day,OffsetDays + 2,OrderRel.ReqDate)
else dateadd(day,OffsetDays,OrderRel.ReqDate)
end
To give you the specific nvar day name:
Format(dateadd(day,OffsetDays,OrderRel.ReqDate), ‘ddddd’)
Hope that helps
Hi Ridgea thanks for your reply- I couldn’t get this one to compile - it didn’t like ‘offset’ days.
I don’t quite understand the logic here-but if its finding if the date in which it falls on is a weekend then add 1 or 2 days, but what if the date it falls on is not a weekend and is in the week but the weekend days have already been accounted for?
Date with the additional days added if Shipment falls on weekend =
case
when datepart(weekday,(dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate))) = 1 then dateadd(day,OrderHed.DeliveryDays_c + 1,ShipHead.ShipDate)
when datepart(weekday,(dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate))) = 7 then dateadd(day,OrderHed.DeliveryDays_c + 2,ShipHead.ShipDate)
else dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate)
end
To give you the specific nvar day name:
Date that the delivery falls on =
Format(dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate), ‘ddddd’)
I was just using OffsetDays as an example
Hope that helps 
Thanks Alex, is this logic checking if the date it falls on is a weekend? as I need to check if any of the days between the dates are a weekend?
Just tried your code and found where ship date = 30-07-19 with delivery days of 7, I expected a receive date of 8/8/19 but instead it calculates the 06-08-19
Another option would be to use your production calendar in Epicor, where you should have setup already the week-end days and holidays for your production schedule (unless you have a week end production, then create a new calendar for your shipping? ).
From Tim Shoemaker (sorry I do not know how to add it nicely from another post… )
Summary
OK… to EXCLUDE HOLIDAYS, here is snippet of C# code that you can insert anywhere you need (BPM, etc)…
It calculates using one of the Epicor Calendars. It still assumes that Saturday and Sunday are non-work days, but it removes any holidays that are declared in the Shop Calendar you have specified.
I borrowed much of the logic from multiple sources on the internet, but also had to create my own little logic to use the Epicor calendar. Note that if you feed a starting date that is a weekend, it automatically moves it to the next working date.
While it seems odd to have to loop to do this calculation, I found no other clean way to do it… I did put some timers on this initially, and even when counting 1000s of days, the routine below only took a very brief number of milliseconds.
// Calculate Promise Date
// inputs:
// LeadTime = the lead DAYS you want to calculate
// StartingDate = the date you want to start counting form
// Outputs:
// pd = Promise date
//
string CalendarToUse = “Shipping” //change this to the epicor calendar you want to calculate against
DateTime pd = StartingDate ?? DateTime.Today; //promise date
bool WorkDay=true;
int RemainingDays = LeadTime+1; //add one to the leadtime
pd = pd.AddDays(-1); //make it yesterday
while (RemainingDays>0)
{
if (pd.DayOfWeek == DayOfWeek.Friday) pd=pd.AddDays(3);
else if (pd.DayOfWeek == DayOfWeek.Saturday) pd=pd.AddDays(2);
else if (pd.DayOfWeek == DayOfWeek.Sunday) pd=pd.AddDays(1);
else pd=pd.AddDays(1);
```
//now make sure that we are not on a holiday
var Holiday = Db.ProdCalDay.Where(r => r.CalendarID == CalendarToUse && r.ModifiedDay == pd).Select( row => new {row.ModifiedDay, row.WorkingDay} ).FirstOrDefault();
WorkDay = (Holiday == null)|| Holiday.WorkingDay; // WorkDay = true; else WorkDay=false;
if (WorkDay) RemainingDays -=1;
Inputs.ErrorText.Value +=string.Format("point1: {0} {1} {2}\n",pd,RemainingDays,(WorkDay)?"":"Holiday");
}
```
Pierre
Hi Thanks we have not locked down our weekends as sometimes we do schedule on these days
You can make another calendar to reference then. It doesn’t need to be used anywhere else. You can have as many calendars as you want.
Try This in a calculated field:
(case when datepart(weekday,Dateadd(day,OrderHed.DeliveryDays_c + (OrderHed.DeliveryDays_c - (datediff(day,ShipHead.ShipDate,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate)) + ((((datepart(wk,ShipHead.ShipDate) - ((datepart(year,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate)) - datepart(year,ShipHead.ShipDate))* 52)) - (datepart(wk,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate))))) * 2))),ShipHead.ShipDate)) = 7 then Dateadd(day,(OrderHed.DeliveryDays_c + 2) + (OrderHed.DeliveryDays_c - (datediff(day,ShipHead.ShipDate,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate)) + ((((datepart(wk,ShipHead.ShipDate) - ((datepart(year,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate)) - datepart(year,ShipHead.ShipDate))* 52)) - (datepart(wk,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate))))) * 2))),ShipHead.ShipDate) else Dateadd(day,OrderHed.DeliveryDays_c + (OrderHed.DeliveryDays_c - (datediff(day,ShipHead.ShipDate,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate)) + ((((datepart(wk,ShipHead.ShipDate) - ((datepart(year,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate)) - datepart(year,ShipHead.ShipDate))* 52)) - (datepart(wk,dateadd(day,OrderHed.DeliveryDays_c,ShipHead.ShipDate))))) * 2))),ShipHead.ShipDate) end)
Thing is if it spans over 2 weekends do you want it to omit all 4 days ?
Hope this helps
1 Like
Hi This looks great! ive tested across 10 different jobs and they seem to calculate correctly.
If it spans over 2 weekends yes I would like it to omit 4 days, does it not?
It does indeed omit 4 days in the majority of cases.
The only things so far i spotted was if it falls on Saturday its a bit temperamental. - So just need to slightly tweak the case statement in there
For example :
I would of expected this to have had a delivery date of 04/12/19.
Just a little tweak and should be decent
Fantastic that’s genius – could you explain to me how it works?
In summary:
Variable 1. Looks at the total days between the 2 dates
Variable 2.Then it off that looks at the number of completed weeks between the “Delivery Date” with 2 methods.
a. Number of completed weeks then multiplies it by 2 (Saturday + Sunday)
b. Number of completed years between the two dates (52 weeks * 2 day weekend)
Variable 3. This total sum of Variable 2 is added to Variable 1
Add Variable 3 to the ShipHead.ShipDate to give you the Delivery date.
The only thing that i have seen so far when playing about with it is that if the delivery falls on a Saturday its deemed an incomplete week (Think a week is Sunday to Sunday - depending on timezone / setup), But sure this can be fixed by just using a case statement and offsetting the calculation slightly
Hope that makes sense ?
I’m attempting to reverse this so I can subtract days instead of adding, and still land on a weekday. I’ll post if I can figure it out.
This is a quick and easy way to get close. I am in the cloud so I can’t call a function to exclude weekends. I am using this to calculate an Order By column using the lead time. Thank you!
1 Like
Epicor should provide some formulas using a calendar. As they provide a formula to add days to current day. Then we would not need to struggle to find a way to get previous or next work day.
Hopefully be part of the Kinetic version !
Go vote for this idea if not yet done… it is indicated Future consideration…
Pierre
1 Like