Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

DateTimeDiff - in regards to "Business Days includes Holidays"

JohnBell
8 - Asteroid

Hi,

 

I'm using the DateTimeDiff function.  This is working fine "DateTimeDiff([Current Date], [Initial Date], "days")...

 

But what I really want is:

1) current date - 1 …. i.e.  I really want "yesterday"

2) I really want "business days"...i.e. not counting Saturday, Sunday, or Holidays - example 7/4.

 

I was hoping the above would work, but I haven't been able to get it working.  Using 6/26/19 as initial date and 7/23/19 as current date, I know it is 18 business days (4th of July holiday).

 

Any ideas?

Thanks!

 

4 REPLIES 4
BenMoss
ACE Emeritus
ACE Emeritus
Hi!

I have created and shared a tool on the public gallery that allows you to calculate the difference between 2 dates in business days.

You specify the days you want to class as working days via the interface and you can load a file which has a lost of bank holidays to exclude.

The macro can be be found here...

https://gallery.alteryx.com/#!app/Working-Day-Time-Difference/5a0ad875f499c708d037257c

Ben
jamielaird
14 - Magnetar

Hi @JohnBell 

 

1) For yesterday's date use:

 

 

DateTimeAdd(DateTimeToday(),-1,"days")

 

 

2) There is no support for business days due to the number of possible public holidays worldwide that would need to be supported. You can skip weekends with a few minor changes to your formula to not count Saturdays and Sundays.

BenMoss
ACE Emeritus
ACE Emeritus
And if you want your latest date to be yesterday rather than today then I would use the function...

Datetimeadd(Datetimetoday(),-1,'days')

Create this as a column before then passing this as your end date selection in the macro.
JohnBell
8 - Asteroid

Thank you everybody!

 

This is very good...I didn't even think of  using the DateTimeAdd with a negative number to get "yesterday".  I'll have to look at the macro too...

 

Thank you very much!

 

John

Labels