DateTimeDiff - in regards to "Business Days includes Holidays"

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!

Date Time

Accepted answers

JohnBell

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

All comments

BenMoss

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

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

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

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

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335