I would handle this with a simple case statement probably. Assuming your first day of the week is Sunday (i.e. 0, you can modify accordingly if not) you can use something like this to calculate date+5DaysExcludingWeekends:
when DayOfWeek([myDate])=0 then DateAdd("day",5,[myDate])
when (DayOfWeek([myDate])>=1) and (DayOfWeek([myDate])<=5) then DateAdd("day",7,[myDate])
when DayOfWeek([myDate])=6 then DateAdd("day",6,[myDate])
Basically, depending on the day of the week, just add 1 or 2 additional days to accomodate the number of weekends in that period.
You aren't very clear with your question, specifically on the Saturday and Sunday portion. Do you want to exclude Saturdays and Sundays completly, or just ignore them if it is day 5? Assuming the latter, you can do something similar to the blow:
if(DateTimeNow() > DateAdd('day',5,[RecieveDate]) and DayOfWeek(DateAdd('day',5,[RecieveDate])) <>5 DayOfWeek(DateAdd('day',5,[RecieveDate])) <>6,"Over 5 Days Late", "Not Late")
If you want to ignore Saturday's and Sundays completly from the count then that's different logic. Please specify.
Ok. The only way I know how to do this (there may be other ways) is to create a table of dates. If you are using MSSQL i can give you a script to populate this (accounting for leap year) for like 30 years. Otherwise you can create one in excel and embed it into your analysis. Then, you'd insert a calculated column to see if it's a weekday or not:
If(DayOfWeek([DateColumn]) = 5 or DayOfWeek([DateColumn]) = 6,"Weekend","Weekday") as [DayTypeFlag]
Then, you will relate this table to your current data table and Count the days based off your static list of dates.
Count(If([DateColumn] >= [RecieveDate] and [DateColumn] <= DateTimeNow(),If([DayTypeFlag] = "Weekday",[DateColumn])) as [DaysLate]
Then, if the number is > 5 you have your threshold.
Shaun Simon - May 25, 2016 - 7:08am
Hi Shaun, I want to skip the Saturday, Sunday and add business day eg. If today is Friday and to add 5 business day in it should skip Sat, sun and add 5 days.