Excluding weekends is a popular topic on the forums. Here are a couple links to other threads which hopefully will help you out:
And here's a link to a knowledge base article about using business days:
Hope this helps.
Thanks, Ben. I have looked over a lot of these. I will go through them again to see if I can make one work.
1 of 1 people found this helpful
Will a Start Time or End Time ever fall on a weekend day?
If not then this calc should work:
DATEDIFF('hour', [Order Date], [Ship Date]) -
(DATEDIFF('week', [Order Date], [Ship Date]) * 48)
EDIT: Except when your dates cross into a new year, then if fails.
Unfortunately I am see some that do fall on weekends. But maybe I can put logic in to handle those. Thanks for the help.
That'll be an interesting calc! If the end time is 10:00 Saturday morning, will you be adding 10 hours (from midnight), or 2 hours (assuming a 8 a.m. start of the day) or 1 hour (assuming a 9 a.m. start of day).
If you figure out all the possible permutations, post them and we can help you account for them all.
Will do. Thanks.
1 of 1 people found this helpful
We recently had an application for this and came up with a solution that excluded hours over the weekend. The formula has a few opportunities for improvement but it works across all possible start and end dates. It is a really long and ugly formula but at least it works! Notes: week diff is Datediff('week', [Created Dt],[Completed Dt]); Weekday Start is DATEPART('weekday',[Created Dt]); and Weekday End is DATEPART('weekday',Completed Dt). In order to get partial hours everything is evaluated at the minute level and then converted to hours. If you don't need to get this granular you could change everything to hours (just remember to take out the conversion logic).
Hopefully this helps someone!
// The first IF statement evaluates items that are created and completed on the same weekend.
// These items are set to 0 since the occurred exclusively over the weekend.
IF [week diff] = 1 and [Weekday Start]=7 and [Weekday End] = 1
ELSEIF [week diff] = 0 and
([Weekday Start] = 1 or [Weekday Start] = 7)
([Weekday End] = 1 or [Weekday End] = 7)
//If the above two conditions are not met we need to calculate how much time occurred of the weekend.
//For every week(second date diff) that elapses 48 hours are subtracted from total hours(first date diff).
DATEDIFF('minute',[Created Dt],[Completed Dt])/60
DATEDIFF('week',[Created Dt],[Completed Dt])*-48
//The following statements are adjustments to the above 48hour subtraction depending
//on what day the item is created or completed.
//If the item is created on Saturday we need to add back the time from 12am Saturday
//morning to its actual created time on Saturday.
IF [week diff] >= 1 and [Weekday Start] = 7
then 24+(DATEDIFF('minute',DATETRUNC('day',[Created Dt]),[Created Dt])-1440)/60
//If the item is completed on a Sunday we need to add back in the time from when the ticket
// completed Sunday to the end of the day Sunday.
IF [week diff] >=1 and [Weekday End] = 1
then 24-(DATEDIFF('minute',DATETRUNC('day',[Completed Dt]),[Completed Dt]))/60
// The following statements subtract weekend time if the item did not last more than 1 week
// If the item was completed on Saturday we need to subtract out them time from 12am Saturday
// until the time the item was completed.
IF [week diff] = 0 and [Weekday End] =7
then -(DATEDIFF('minute',DATETRUNC('day',[Completed Dt]),[Completed Dt]))/60
// If the ticket was created on Sunday we need to subtract out the time from when the item was created
// and the end of day Sunday.
IF [week diff] = 0 and [Weekday Start] = 1
then (DATEDIFF('minute',DATETRUNC('day',[Created Dt]),[Created Dt])-1440)/60
Corey you came through in the clutch on this one. I used your formula and although complex, seems to be working. This probably took some time to think about the logic for it.
The only thing I can think about that might not be accounted for is holidays which wasn't asked in the op. I think it would be fairly easy to make a calculated field based off of a data source with the holiday dates and then add it into your initial IF statement.