Date/Time variables are labeled automatically based on how you configure a Date/Time variable. However, sometimes you may want to format these labels, especially if your report is not in English or you have non-standard time periods. You can now do this directly on the variable itself; see Customize the labels and categories of your Date/Time variable.
Alternatively, if you're looking to abbreviate long month names to shorter month names, you can use an automation; see How to Abbreviate Month Labels in Tables and Visualizations. Otherwise, you can create a more custom abbreviation format or labeling by modifying one of the examples below.
Method - Relabeling dates with spaces
Method - Relabeling dates with forward slashes /
Method - Relabeling any date to anything
Method - Relabeling dates with spaces
You can relabel a table with dates that have spaces:
- With your table selected, click on Data > Rules > + > New Custom Rule (Write your own JavaScript).
- Click Edit JavaScript.
-
You can delete the code currently there - it's all comments. Paste in the code below and modify as appropriate according to the comments (prefixed by //):
//////////
//Change date labels
//////////
////Create UI for the user to select if dates are in the rows or columns
//////////
//give it a heading
form.setHeading("Specify Dates Labels");
let description = form.newLabel("Rename date labels");
description.lineBreakAfter = true;
//create the prompt for where the dates are
let label = form.newLabel("Dates in");
//create the dropdown for the options columns rows
let row_or_column_box = form.newComboBox("rowcol", ["columns", "rows"]);
//set the default to columns
row_or_column_box.setDefault("columns");
//create the final UI putting together the elements above
form.setInputControls([description, label, row_or_column_box]);
//create variable that's TRUE if the user selected rows in the dropdown
let do_rows = row_or_column_box.getValue() == "rows";
//setup Rule name shown in the list to identify it in document
//CHANGE the name below inside the "" to something more specific
//if using this on more than one date variable
form.setSummary("Abbreviate dates names in " + row_or_column_box.getValue());
//////////
//// Create loop with logic to relabel labels
//////////
//create list of labels to loop through based on if we're going to loop through the rows or columns
let labels = do_rows ? table.rowLabels : table.columnLabels;
//setup a function to swap out the labels with the new ones
labels = labels.map(function (label) {
//below is based on dates containing spaces like January 2019 and Jan-Mar 2019
//split each label into two parts at the space first part=month second=year
let label_elements = label.split(" ");
//create new variable month_prefix for the first part of the label before the year
let month_prefix = label_elements[0];
//check the month part of the label for a particular month/text
//if found, change the month part to the relabeled part inside the if statement
//CHANGE the line after the if and else if statements to the date label you'd like use
if (month_prefix.indexOf("January") == 0)
month_prefix = "Jan.";
else if (month_prefix.indexOf("February") == 0)
month_prefix = "Feb.";
else if (month_prefix.indexOf("March") == 0)
month_prefix = "Mar.";
else if (month_prefix.indexOf("April") == 0)
month_prefix = "Apr.";
else if (month_prefix.indexOf("May") == 0)
month_prefix = "May.";
else if (month_prefix.indexOf("June") == 0)
month_prefix = "Jun.";
else if (month_prefix.indexOf("July") == 0)
month_prefix = "Jul.";
else if (month_prefix.indexOf("August") == 0)
month_prefix = "Aug.";
else if (month_prefix.indexOf("September") == 0)
month_prefix = "Sep.";
else if (month_prefix.indexOf("October") == 0)
month_prefix = "Oct.";
else if (month_prefix.indexOf("November") == 0)
month_prefix = "Nov.";
else if (month_prefix.indexOf("December") == 0)
month_prefix = "Dec.";
//QUARTERLY months relabeling
else if (month_prefix.indexOf("Jan-Mar") == 0)
month_prefix = "Q1";
else if (month_prefix.indexOf("Apr-Jun") == 0)
month_prefix = "Q2";
else if (month_prefix.indexOf("Jul-Sep") == 0)
month_prefix = "Q3";
else if (month_prefix.indexOf("Oct-Dec") == 0)
month_prefix = "Q4";
//create variable for second part of label split by space the year
let year = label_elements[1];
//if there isn't two parts to the label the dates are all in one year so leave year off
//otherwise return the new label with the year
if (label_elements.length != 2)
return month_prefix;
//CHANGE " " below if you want to separate the prefix and year by something other than a space
else return month_prefix + " " + year;
});
//////////
//// With relabels created above, now add them to the table
//////////
//if use selected rows set rowLabels otherwise set columnLabels to the new labels
if (do_rows)
table.rowLabels = labels;
else
table.columnLabels = labels; - Click OK -- note that if the wrong columns/rows are in the dropdown, you may receive an error, but this will go away once the correct columns/rows option is selected.
Method - Relabeling dates with forward slashes /
- With your table selected, click on Data > Rules > + > New Custom Rule (Write your own JavaScript).
- Click Edit JavaScript.
-
You can delete the code currently there - it's all comments. Paste in the code below and modify as appropriate according to the comments (prefixed by //):
//////////
//Change date labels
//////////
////Create UI for the user to select if dates are in the rows or columns
//////////
//give it a heading
form.setHeading("Specify Dates Labels");
let description = form.newLabel("Rename date labels");
description.lineBreakAfter = true;
//create the prompt for where the dates are
let label = form.newLabel("Dates in");
//create the dropdown for the options columns rows
let row_or_column_box = form.newComboBox("rowcol", ["columns", "rows"]);
//set the default to columns
row_or_column_box.setDefault("columns");
//create the final UI putting together the elements above
form.setInputControls([description, label, row_or_column_box]);
//create variable that's TRUE if the user selected rows in the dropdown
let do_rows = row_or_column_box.getValue() == "rows";
//setup Rule name shown in the list to identify it in document
//CHANGE the name below inside the "" to something more specific
//if using this on more than one date variable
form.setSummary("Abbreviate dates names in " + row_or_column_box.getValue());
//////////
//// Create loop with logic to relabel labels
//////////
//create list of labels to loop through based on if we're going to loop through the rows or columns
let labels = do_rows ? table.rowLabels : table.columnLabels;
//setup a function to swap out the labels with the new ones
//below is based on labels showing a range of dates similar to 1/1/2022-2/1/2022
//the below function takes the second date from the date range
//using the month of that date, it then relabels the header
labels = labels.map(function (label) {
//split each label into the two date parts at the hyphen
let thedates = label.split("-");
//use the second/ending date
let second_date = thedates[1];
//split each label into various date components using /
let label_elements = second_date.split("/");
//check to see that the parsing is working - if doesnt return 3 pieces of the date use the current label
if(label_elements.length < 3) return label;
//create new variable that is the month_prefix for the month part of the label
//CHANGE if you're working with US dates use [0] since month is before the first / for DD/MM/YYYY use [1] below
let month_prefix = label_elements[0];
//check the month part of the label for a particular month/text
//if found, change the month part to the relabeled part inside the if statement
//CHANGE the line after the if and else if statements to the date label you'd like use
if (month_prefix.indexOf("10") == 0)
month_prefix = "Oct.";
else if (month_prefix.indexOf("11") == 0)
month_prefix = "Nov.";
else if (month_prefix.indexOf("12") == 0)
month_prefix = "Dec.";
else if (month_prefix.indexOf("1") == 0)
month_prefix = "Jan.";
else if (month_prefix.indexOf("2") == 0)
month_prefix = "Feb.";
else if (month_prefix.indexOf("3") == 0)
month_prefix = "Mar.";
else if (month_prefix.indexOf("4") == 0)
month_prefix = "Apr.";
else if (month_prefix.indexOf("5") == 0)
month_prefix = "May.";
else if (month_prefix.indexOf("6") == 0)
month_prefix = "Jun.";
else if (month_prefix.indexOf("7") == 0)
month_prefix = "Jul.";
else if (month_prefix.indexOf("8") == 0)
month_prefix = "Aug.";
else if (month_prefix.indexOf("9") == 0)
month_prefix = "Sep.";
//create variable for second part of label split by space the year
let year = label_elements[2];
//CHANGE " " below if you want to separate the prefix and year by something other than a space
return month_prefix + " " + year;
});
//////////
//// With relabels created above, now add them to the table
//////////
//if use selected rows set rowLabels otherwise set columnLabels to the new labels
if (do_rows)
table.rowLabels = labels;
else
table.columnLabels = labels; - Click OK -- note that if the wrong columns/rows are in the dropdown, you may receive an error, but this will go away once the correct columns/rows option is selected.
Method - Relabeling any date to anything
You can modify labels in even more custom ways by adding your own custom code into the general template:
//////////
//Change date labels
//////////
////Create UI for the user to select if dates are in the rows or columns
//////////
//give it a heading
form.setHeading("Specify Dates Labels");
let description = form.newLabel("Rename date labels");
description.lineBreakAfter = true;
//create the prompt for where the dates are
let label = form.newLabel("Dates in");
//create the dropdown for the options columns rows
let row_or_column_box = form.newComboBox("rowcol", ["columns", "rows"]);
//set the default to columns
row_or_column_box.setDefault("columns");
//create the final UI putting together the elements above
form.setInputControls([description, label, row_or_column_box]);
//create variable that's TRUE if the user selected rows in the dropdown
let do_rows = row_or_column_box.getValue() == "rows";
//setup Rule name shown in the list to identify it in document
//CHANGE the name below inside the "" to something more specific
//if using this on more than one date variable
form.setSummary("Abbreviate dates names in " + row_or_column_box.getValue());
//////////
//// Create loop with logic to relabel labels
//////////
//create list of labels to loop through based on if we're going to loop through the rows or columns
let labels = do_rows ? table.rowLabels : table.columnLabels;
//setup a function to swap out the labels with the new ones
labels = labels.map(function (label) {
////WRITE CODE TO PARSE THE CURRENT DATE LABEL
////WRITE IF/ELSE CODE TO USE CURRENT LABEL TO RELABEL AS NEEDED
////WRITE CODE TO RETURN LABEL IN CUSTOM FORMAT
});
//////////
//// With relabels created above, now add them to the table
//////////
//if use selected rows set rowLabels otherwise set columnLabels to the new labels
if (do_rows)
table.rowLabels = labels;
else
table.columnLabels = labels;
Next
How to Conduct Significance Tests by Comparing to Previous Time Periods
How to Insert a Date Control and How to Work with Date Ranges Using R
How to Test Against the Previous Period without a Date/Time Variable