Table of Contents
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)
See also
- Functions: Date() function (to convert text-based dates into valid date/time values)