This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 11-15-201602:33 PM - edited on 05-21-201902:12 PM by SydneyF
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.
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()':
Use the same method if you want just the date or 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 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.
WeekStartDate: date in yyyy-mm-dd format and data type = Date; week begins on Sunday with the possible exception of week 1.
WeekEndDate: date in yyyy-mm-dd format and data type = Date; week ends on Saturday with the possible exception of week 52 (or 53).
MonthStartDate: date in yyyy-mm-dd format and data type = Date.
MonthEndDate: date in yyyy-mm-dd format and data type = Date.
QuarterStartDate: date in yyyy-mm-dd format and data type = Date.
QuarterEndDate: 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.