Hi!
I have a ShipHead.ShipDate which I want to add ship via delivery days to, but within the date add I do not want it to add the weekends of Saturday and Sunday into the calculation as the carriers do not transit over weekends.
My current Dateadd:
Any advice greatly appreciated, ive researched datadd and cannot find any examples where someone is adding one field to another field and excluding weekends …
Perhaps you can check what the day name is and add dateadd portion inside of, if its saturday add + 2 and if its sunday add + 1 perhaps.
Keeping it simple, can be refactored with IIF
Starter Snippet:
CASE
WHEN DATENAME(w, '11/5/2019') = 'Sunday' THEN
'Its Sunday!'
WHEN DATENAME(w, '11/5/2019') = 'Saturday' THEN
'Its Saturday!'
ELSE
'Its a Weekday!'
END
You can find several posts about it on forums:
https://www.epiusers.help/search?q=datename
CASE WHEN DATENAME(WEEKDAY,POHeader.OrderDate) = 'Sunday'
THEN DATEADD(dd,5,POHeader.OrderDate)
WHEN DATENAME(WEEKDAY,POHeader.OrderDate) = 'Saturday'
THEN DATEADD(dd,6,POHeader.OrderDate)
ELSE DATEADD(dd,7,POHeader.OrderDate)
END
Hi Thanks. Im not sure if that would work as wouldn’t it need to check every day date its adding to see if it’s a sat or sun?
If you have access to the database, you can create a function to do this and call it in BAQ editor instead of DATEADD
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
You code try something like this assuming shipdate is not on a weekend and you are not skipping holidays:
DATEADD(DAY,OrderHed.DeliveryDays_c % 5,DATEADD(WEEK,(OrderHed.DeliveryDays_c/5),ShipHead.ShipDate))tried this- it doesn’t work - it compiles but still gives me date which includes weekends
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… )
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
