User Tools

Site Tools


transformations:makedatetimecolumns

MAKE DATE/TIME COLUMNS

Category: Transform / Advanced


Description

Take a column with dates and/or times and produce one or more columns of various components (e.g. year, weekday, hour, minutes, etc.) of the original dates or time period names.


Use cases

This action is useful for breaking dates and times into components for downstream date/time calculations:

  • Calculating "age" from "date of birth".
  • Calculating "tenure" from "hire date".
  • Finding the days' or years' difference between two dates.
  • Finding the hours' or minutes' difference between two times.
  • Counting down "days remaining" between "today" and a defined "expiration date".


Action settings

SettingDescription
ColumnSelect the column containing date/time values to derive the individual date/time components from.


Date components

OptionDescription
Formatted dateCreates a date value in the format provided (enter a valid date format). See format function for valid date/time format string options).
YearOutputs the numeric 4-digit year (e.g. "2021").
QuarterOutputs the quarter as a string value (e.g. "Q1").
Year-quarterOutputs the year and quarter as a string value (e.g., "2021-Q1").
Month nameOutputs the 3-letter month abbreviation (e.g., "Oct"). See below for output format options.
Year-monthOuptuts the year and abbreviated month name (e.g., "2021-Mar"). See below for output format options.
DayOutputs the numeric day number (e.g., 5).
Week dayOutputs the 3-letter day abbreviation (e.g., "Sat"). See below for output format options.
Day of yearOutputs the numeric sequential day number within the year (e.g., "284").
ISO week numberOutputs the numeric sequential week number within the year (e.g., "38").
Month startOutputs the fully-formatted date value of the first day of the month (e.g., "01-Nov-2021"). When this option is selected,
also select the starting day of the week (Sunday or Monday).
Month endOutputs the fully-formatted date value of the last day of the month (e.g., "31-Oct-2021"). When this option is selected,
also select the starting day of the week (Sunday or Monday).
Week startOutputs the fully-formatted date value of the first day of the week the source date falls in (e.g., "04-Oct-2021").
Week endOutputs the fully-formatted date value of the last day of the week the source date falls in (e.g., "20-Jan-2021").


Month, Year-month, Week day output format options

OptionDescription
EnglishOutputs the text value in English.
LocalOutputs the text value in the local language based on your system settings.
NumberOutputs the value as a number instead of text.


Time components

OptionDescription
Hour (12-hour)Outputs the hour as a numeric value based on a 12-hour clock (e.g., 1 to 12).
Hour (24-hour)Outputs the hour as a numeric value based on a 24-hour clock (e.g., 0-23).
AM/PMOutputs whether the source time falls within the first 12 hours of the day ("AM") or the second ("PM"), as a text value.
MinutesOutputs the minutes as a numeric value (e.g. 0-59).
SecondsOutputs the seconds as a numeric value (e.g., 0-59).
MillisecondsOutputs the milliseconds as a numeric value (e.g., 0-999).


Remarks

Dates without time values are calculated based on a default time of 12:00 AM.

The options in this action only work on values stored in the date/time format, not on dates or times stored as text values (which will produce <empty> results). Use the Date() function or the Convert data type action to convert text values into proper date/time values.

How to determine if values are actually date/times, or being stored as text
There are several ways to confirm values appearing to be date/times are actually being stored as such:

  • Cell alignment: Date/time values will be right-aligned in the dataset grid, while text-based values will be left-aligned.
  • The Column Profile/Filter window: In the Values tab, you will see date/time values leading with the date serial number (e.g., 32888 15-Jan-1990), while text-based values will only show the value (e.g., 15-Jan-1990). In the Metadata tab, the number format for date/times will be "dd-MMM-yyyy" while text values will not have a number format.
  • Analysis View, Table metadata: Date/time column values will show counts under "numbers" and "integers" while text-based values will show counts under "text". (These same results will appear using the Table metadata action.)


Examples

Example: Create columns with year-quarter, week day, and ISO week number.

Source table:

Date
2020-Nov-28
2020-Nov-29
2020-Nov-30


Action parameters:

Column is "Date"
Select options "Year-quarter", "Week day" (output "English"), and "ISO week number".


Result table:

Date Year-quarter Week day ISO Week number
2020-Nov-28 2020-Q4 Sat 48
2020-Nov-29 2020-Q4 Sun 48
2020-Nov-30 2020-Q4 Mon 49


Community examples


See also

transformations/makedatetimecolumns.txt · Last modified: 2021/07/19 02:24 by craigt