Alteryx for Excel Users: Getting data for dates

[WayneWooldridge]

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.

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

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.

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

Results in:

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

Just date:

Just time:

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.

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.