{{ transformations:MakeDateTimeColumnsAction.png}}
====== 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 [[syntax:functions:format|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 [[syntax:functions:date|Date() function]] or the [[transformations:convert|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====
  * [[https://community.easymorph.com/t//1587/7|How to format the duration in more than 24h]] ([[https://community.easymorph.com/uploads/short-url/w6CnxwiidYjcJHNRrmUIXI74NC2.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //5//)
  * [[https://community.easymorph.com/t//2297/2|Excel: Import Date Headings as number]] ([[https://community.easymorph.com/uploads/short-url/ppgiXxeiQbK0O3owwca3pxAsOyH.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //New header//; Action position: //4//)

\\ 
=====See also=====
  * [[transformations:convert|Convert data type]]
  * [[syntax:functions#date_time_functions|Functions:  List of date/time functions]]
  * [[syntax:functions:date|Functions:  Date() function]] //(to convert text-based dates into valid date/time values)//