Differences

This shows you the differences between two versions of the page.

--- transformations:makedatetimecolumns [2021/04/02 04:33] – dmitry
+++ transformations:makedatetimecolumns [2021/07/15 12:27] – [Examples] craigt
@@ Line 1: / Line 1: @@
-===== Make Date/Time Columns =====
+{{ transformations:MakeDateTimeColumnsAction.png}}
+====== MAKE DATE/TIME COLUMNS ======
+Category: Transform / Advanced \\
-Take a column with dates and produce one or several columns with various components (e.g. year, or weekday) of the original dates, or time period names.
+\\
+=====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.\\
-Column that can be created:
+\\
-  * Formatted date
+=====Use cases=====
-  * Year
+This action is useful for breaking dates and times into components for downstream date/time calculations:
-  * Quarter (e.g. Q1)
+  * Calculating "age" from "date of birth".
-  * Year-quarter (e.g. 2019-Q1)
+  * Calculating "tenure" from "hire date".
-  * Month name
+  * Finding the days' or years' difference between two dates.
-  * Year-month (e.g. 2019-Apr)
+  * Finding the hours' or minutes' difference between two times.
-  * Day
+  * Counting down "days remaining" between "today" and a defined "expiration date".
-  * Week day
-  * Day of year
-  * ISO week number
-  * Month start
-  * Month end
-  * Week start
-  * Week end
-  * Hour (12-hour)
-  * Hour (24-hour)
-  * AM/PM
-  * Minutes
-  * Seconds
-  * Milliseconds
-**Example**
+\\
+=====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). |
-Table before:
+\\
+=====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=====
+**Objective:** Create columns with year-quarter, week day, and ISO week number.\\
+**Source table:**
 ^ Date ^
 | 2020-Nov-28  |
 | 2020-Nov-29  |
 | 2020-Nov-30  |
+\\
-Objective: create columns with year-quarter, week day, and ISO week number.
+**Action parameters:**
+> Column is "Date"
-Table after:
+> Select options "Year-quarter", "Week day" (output "English"), and "ISO week number".
+\\
+**Result:**
 ^ Date ^ Year-quarter ^ Week day ^ ISO Week number ^
 | 2020-Nov-28  | 2020-Q4  | Sat  |  48|
@@ Line 41: / Line 88: @@
 | 2020-Nov-30  | 2020-Q4  | Mon  |  49|
-** See also **
+\\
-  * [[transformations:convert|Convert Data Types]]
+====Community examples====
-  * [[transformations:calendar|Calendar]]
+  * [[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//)
-  * [[syntax:functions#date_time_functions|List of date/time functions]]
+  * [[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)//