Easiest way to do datetimediff no weekends

[wonka1234]

Hi,

How can I do datetimediff and exclude weekends? I see solutions online that are kind of hard.

I do have a business date generator, not sure if that would help.

I am trying to make the workflow as least confusing as possible.

Ie 

Today	Due Date Formatted	diff
8/15/2023	8/11/2023	-4

I would expect -2 here instead.. 

thanks

[Felipe_Ribeir0]

Hi @wonka1234 

Try the attached workflow. This way will avoid the generate rows tool, make the workflow more simpler and performatic.

Reference: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Datetimediff-using-only-weekda...

[wonka1234]

@Felipe_Ribeir0  thank you!!

one question, what if its reverse? I only see negative numbers.. ie Id want this to show 2...

Today Due Date Formatted
2023-08-15 2023-08-17

[Felipe_Ribeir0]

@wonka1234 

To show the difference as a positive number, just remove the negative signal from the BusinessDays formula.

[wonka1234]

@Felipe_Ribeir0  

sorry i mispoke.. my logic may be harder then ithought...

what your original formula gives me :

Today	Due Date Formatted	BusinessDays
8/15/2023	8/17/2023
8/15/2023	8/28/2023
8/15/2023	8/15/2023	0
8/15/2023	8/10/2023	-3
8/15/2023	8/18/2023
8/15/2023	8/16/2023

expected:

Today	Due Date Formatted	BusinessDays
8/15/2023	8/17/2023	2
8/15/2023	8/15/2023	0
8/15/2023	8/10/2023	-3
8/15/2023	8/18/2023	3
8/15/2023	8/16/2023	1

does this make sense?

[Felipe_Ribeir0 · Accepted solution]

Hi @wonka1234 

See this version:

[wonka1234]

@Felipe_Ribeir0  thx! works perfect..now i dont really understand the logic though!

[Felipe_Ribeir0]

@wonka1234 to be honest with you i never took the time to really undestand this formula, i just use it as a black box that works 😅. But according to chatgpt, this is what is happening:

1. IIF([StartDate]>[EndDate], Null(), ...):

   • This part checks if the [StartDate] is greater than [EndDate]. If this condition is met (meaning the start date is after the end date), it returns Null(), indicating an invalid input. This is because you can't have a negative number of business days.

2. 1 + ...:

   • This part adds 1 to the calculation. This is because you want to include the start date in the count of business days.

3. ((DateTimeDiff([EndDate],[StartDate],"days")*5 - ([StartWeekday]-[EndWeekday])*2) / 7):

   • DateTimeDiff([EndDate],[StartDate],"days") calculates the difference in days between the end date and start date. This gives you the total number of days between the two dates, including weekends.
   • Multiplying by 5 (* 5) converts the total days into "workweek days" (assuming a 5-day workweek). This accounts for weekdays and excludes weekends.
   • ([StartWeekday]-[EndWeekday])*2 calculates the difference in weekdays between the start and end dates, then multiplies by 2 to account for the potential effect of weekends.
   • Subtracting this difference from the total workweek days adjusts for weekends that might be between the start and end dates.

4. IIF([EndWeekday]==6,-1,0):

   • This part checks if the end weekday is a Saturday (6). If it is, it subtracts 1 from the calculation. This accounts for the fact that if the end date is a Saturday, you want to exclude it from the business days count.

5. IIF([StartWeekday]==0,-1,0):

   • This part checks if the start weekday is a Sunday (0). If it is, it subtracts 1 from the calculation. This is because if the start date is a Sunday, you want to exclude it from the business days count.

6. -1:

   • Finally, subtracting 1 from the overall calculation adjusts for the addition of 1 at the beginning. This ensures that the start date is not counted twice.

[ArnaldoSandoval]

Hi @wonka1234 

Several months ago I assisted another member of the community on this post Calculating Aging (only business days) in that thread I wrote:

I ran your dates with the workflow posted in April, I found that one of the date ranges is backwards, I mean the range between Aug-15 and Aug-10, here the start date is after the end-date, which is not what the formula expect, so the workflow takes care of these scenarios.

Now based on your post, you are expecting 3 days between August 15 and 10, but it is actually 4; look at the screenshot, Please clarify why you expect 3 days instead of 4 ?

The following article How many weeks are between two dates? was used to create the formula.

hth

Arnaldo

[wonka1234]

@ArnaldoSandoval my original logic added a day to the date because of my business date generator. I used record ID to map to business days in a very confusing way. As you can see the date string started at 1/2/2023.. I know its very confusing.

It was like this : 

RecordID	Column	Date	Dates	Day	Date String
1	dummy	1/1/2023	1/2/2023	Monday	1/2/2023
2	dummy	1/1/2023	1/3/2023	Tuesday	1/3/2023
3	dummy	1/1/2023	1/4/2023	Wednesday	1/4/2023

so I suppose if i was looking for 2023-08-10, it would have really mapped to the 2023-08-11...  meaning 3 days.