Count number of days

[mlozovskaya]

What formula would I use to count number of days between 2 specific dates as an example

6.12.2021 - 5.10.2021=33 days

[Luke_C]

Hi @mlozovskaya 

You would use a 'DateTimeDiff' function. See attached. I also had to use a datetimeparse function to get your dates into the Alteryx date format (yyyy-mm-dd). Find more info on datetime functions here.

DatetimeDiff(datetimeparse([Date1],'%m.%d.%Y'),datetimeparse([Date2],'%m.%d.%Y'),'days')

[mlozovskaya]

Do I need to replace the date format to the string for my Date 1 and Date 2 for formula to work? Right now my input is 6/21/2021, not 6.21.2021 and system does not recognize that format

[Luke_C]

Hi @mlozovskaya 

Then you just update the datetimeparse functions to use / instead of . (see below). I strongly suggest bookmarking the link I posted above, it's come in handy more times than I can count!

DatetimeDiff(datetimeparse([Date1],'%m/%d/%Y'),datetimeparse([Date2],'%m/%d/%Y'),'days')

[mlozovskaya]

I convert my date column to string and performance wend down drastically, is a way for me to keep date format for the source columns and still apply formula?

[Luke_C · Accepted solution]

Hi @mlozovskaya, please provide sample data. I don't understand your question. The solution I provided allows for you to keep the date formatted as is (mm/dd/yyyy) and still apply the formula. Based on what information I see, your date column should already be a string by virtue of it's format - not sure where you'd be converting.

The alternative is to create two new date fields and do the datetimediff function on those. Seeing some sample data might help get to a better solution. 

[mlozovskaya]

I followed your logic and make changes to my formula and it works! Thank you for the prompt response 

[Luke_C]

Great! Glad you're all set 🙂

[mlozovskaya]

@Luke_C  what would be the formula if I need to compare the current date with date 1? is a way to add a current date first?

[Luke_C]

Hi @mlozovskaya 

DateTimeToday() returns today's date. So something like DateTimeDiff(DateTimeToday(),[Date1],'days') would do the trick (assuming date 1 is already in a date format). 

All things date functions can be found on this page: https://help.alteryx.com/20213/designer/datetime-functions