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

Setting	Description
Column	Select the column containing date/time values to derive the individual date/time components from.

Date components

Option	Description
Formatted date	Creates a date value in the format provided (enter a valid date format). See format function for valid date/time format string options).
Year	Outputs the numeric 4-digit year (e.g. "2021").
Quarter	Outputs the quarter as a string value (e.g. "Q1").
Year-quarter	Outputs the year and quarter as a string value (e.g., "2021-Q1").
Month name	Outputs the 3-letter month abbreviation (e.g., "Oct"). See below for output format options.
Year-month	Ouptuts the year and abbreviated month name (e.g., "2021-Mar"). See below for output format options.
Day	Outputs the numeric day number (e.g., 5).
Week day	Outputs the 3-letter day abbreviation (e.g., "Sat"). See below for output format options.
Day of year	Outputs the numeric sequential day number within the year (e.g., "284").
ISO week number	Outputs the numeric sequential week number within the year (e.g., "38").
Month start	Outputs 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 end	Outputs 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 start	Outputs the fully-formatted date value of the first day of the week the source date falls in (e.g., "04-Oct-2021").
Week end	Outputs 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

Option	Description
English	Outputs the text value in English.
Local	Outputs the text value in the local language based on your system settings.
Number	Outputs the value as a number instead of text.

Time components

Option	Description
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/PM	Outputs whether the source time falls within the first 12 hours of the day ("AM") or the second ("PM"), as a text value.
Minutes	Outputs the minutes as a numeric value (e.g. 0-59).
Seconds	Outputs the seconds as a numeric value (e.g., 0-59).
Milliseconds	Outputs 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

How to format the duration in more than 24h (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 5)
Excel: Import Date Headings as number (Project; Module: Main; Group: Group 1; Table: New header; Action position: 4)

EasyMorph Help

Table of Contents