We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Alteryx for Excel Users: Getting data for dates


In this posting, we'll take a look at Excel functions that return today's date and current time. Then we'll see how to use Alteryx to do the same thing. We'll take this astep furtherand show how Alteryx can be used to return a large number of date-related information for any date using macros and apps.

To get today's date in Excel, you use the =TODAY() function.

Dates 01.png

And the =NOW() function will return today's date and time.

Dates 02.png

You can format date and time the way you'd like (eg. Nov-10 or November 10, 2016 instead of 11/10/2016).

Alteryx also has a couple of ways to get today's date and time. The first is macro available in the In/Out toolset called 'Data Time Now'. The tool's configuration provides many options for how you'd like to see the data, including date as well as date and time.

Dates 04.png

The other method is to use a tool (such as the Formula Tool) where an expression can be used with the function 'DateTimeNow()':

Dates 05.png

Results in:

Dates 06.png

Use the same method if you want just the date or time.

Just date:

Dates 07.png

Dates 08.png

Just time:

Dates 09.png

Dates 10.png

There is similar function called 'DateTimeToday()' which will return the current data as of midnight (so the time comes back as 00:00:00).

What if you want information about a date other than today, however? I've written about calendar and date aggregation before and have made a calendar macro available for anyone to use. If you have a date in yyyy-mm-dd format, you join it to the Date field in the macro whichreturns the following fields:

Date: yyyy-mm-dd format; includes every day beginning 2000-01-01 through 2099-12-31.

Year: yyyy format.

Quarter: numeric representation of quarter (1, 2, 3 or 4 rather than Q1, Q2, Q3 or Q4).

Month: numeric representation of month; NO leading zeros.

MonthName: January, February, March, April, May, June, July, August, September, October, November and December; completely spelled out rather than an abbreviation.

WeekNumber: numeric representation of week; generally values range from 1–52, but occasionally a year will have a week 53; weeks 1 and 52 (or 53) may be partial weeks (i.e. less than seven days).

Day: corresponds to the calendar date with values from 1-31 (for months with 31 days); NO leading zeros.

DayName: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday; spelled out rather than abbreviations; Sunday is the beginning of the week.

DayYear: day of year; values range from 1-365 except for leap years which have a day 366.

DayQuarter: day of quarter; values range from 1-92.

DayWeek: numeric representation of week where 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday and 7=Saturday.

Week StartDate: date in yyyy-mm-dd format and data type = Date; week begins on Sunday with the possible exception of week 1.

Week EndDate: date in yyyy-mm-dd format and data type = Date; week ends on Saturday with the possible exception of week 52 (or 53).

Month StartDate: date in yyyy-mm-dd format and data type = Date.

Month EndDate: date in yyyy-mm-dd format and data type = Date.

Quarter StartDate: date in yyyy-mm-dd format and data type = Date.

Quarter EndDate: date in yyyy-mm-dd format and data type = Date.

I've taken this a step further and created an app with the calendar macro embedded in it which allows a user to select a date and the fields they want returned at run time.

Dates 11.png

I've made a couple of version of the Calendar macro; one where the week begins on a Sunday and the other where the week begins on Monday. In the attached app, the macro where the week begins on a Sunday is used but can be easily replaced by the one beginning on Monday.