community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Alteryx for Excel Users: Getting data for dates

Alteryx
Alteryx
Created on

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 a step further and 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 which returns 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.  

Attachments