UnifyDateTime command: automatically reformat dates and time across ranges

XLTools Automation tool allows you to automate tasks in Excel by creating automation commands in a table, without having to create VBA macros.

This tutorial will take you through the setup of the UnifyDateTime automation command. Use it when you need to unify date and time format across worksheets before further calculations.

Note:For non-recurring tasks, use the standard Unify Date Format tool on XLTools ribbon.

Before you begin, add the Automation tool to Excel

Automation is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Download XLTools for Excel

– free trial gives you 14 days of full access to all features.

Step-by-step example: how to use the UnifyDateTime command

Say, you have an Excel table with invoices and payment dates. Every week or so, a few of your colleagues update the file. But everyone enters dates in a different format. So each time you need to process invoices, you have to standardize the date format.

When you automate this process with the UnifyDateTime command, you can unify date format across workbooks in a button click. Follow the steps below to execute the command yourself.

Feel free to download the sample file used in this tutorial.

Download sample file (xlsx)

1. Insert the UnifyDateTime command

Let’s take a look at the Excel spreadsheet with dates. In our example, the spreadsheet contains the worksheet "Invoices" with invoice date, payment due and date paid. See that date values display differently.
Add a new worksheet Name it, for example, “AUTO”. This worksheet will hold automation commands.
Tip:it’s best to keep automation commands on a separate worksheet, not to interfere with data.
Select a starting cell where to place the command, e.g. A1.
Open XLTools tab In the Automation group, open the drop-down list Select UnifyDateTime.
See the table with the automation command is now added on the worksheet.

2. Specify parameters and define how to unify dates and time

Once you’ve inserted the UnifyDateTime command, specify its parameters. This automation table defines how to apply a different format to dates.

XLTools.UnifyDateTime
ApplyTo:	ThisWorkbook
Reformat:	OnlyDate
DateTimeFormat:	m/d/yyyy

What each line in this command reads:

ApplyTo: unify date format across the entire workbook.
Reformat: apply new format only to dates.
DateTimeFormat: apply the "m/d/yyyy" format.

See other available options to use in parameters.

3. Execute the command and review the result

Select the table of the automation command In the Automation group, click Execute Commands.
Review the result – all dates are now standardized to a single unified format:

Done! Now you can assign a custom button to this command.

4. Assign a custom button to the command

If you need to run the command over and over again, add a custom button on the ribbon. So that next time you can execute the command in a click.

In the Automation group, click Add Custom Buttons.
Name the button, e.g. "Unify dates" Specify the range of the UnifyDateTime command Click Save.
Done! The custom button will appear on XLTools tab. Now every time you click this button, you will execute the command.

Parameters and available options

When you create automation commands in Excel, this file with commands must be located in the same destination as the source data:

Add automation commands in the same workbook as your source data. We recommend that you place the commands on a separate worksheet not to interfere with your data.
Or, add automation commands in a separate workbook. Save this automation file in the same folder as the source data.

Below are all parameters and available attributes for the UnifyDateTime command.

Tip:select a cell and see a helpful tooltip. Some attributes also have a drop-down list of available options to choose from.

ApplyTo:

This parameter specifies which data should be processed.

Required parameter, cannot be blank
If you refer to a source workbook, it can be open or closed
The source workbook must be located in the same destination as the automation file

Options	Meaning
ThisWorkbook	Unify dates/time in the active workbook
ThisWorksheet	Unify dates/time on the active worksheet
AllOpenWorkbooks	Unify dates/time across all open workbooks
SelectedRange	Unify dates/time in a selected range

Range

If you selected ApplyTo: SelectedRange, this parameter helps specify which data range should be processed.

Required parameter, cannot be blank
If you refer to a workbook, it can be open or closed
The source workbook must be located in the same destination as the automation file

Options	Meaning
Sheet1!A1:B2	Range A1:B2 on Sheet1 in the active workbook
Sheet1!A:C	Entire A, B, C columns on Sheet1 in the active workbook
Sheet1!1:3	Entire 1, 2, 3 rows on Sheet1 in the active workbook
A1:B2	Range A1:B2 on the current worksheet
Sheet1!	Entire Sheet1 in the active workbook
Sheet1!Table1	Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[Column1]	Column1 in Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[[Column1]:[Column3]]	Columns 1, 2, 3 in Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[#All]	Table1 on Sheet1 in the active workbook, with table headers
Sheet1!Table1[[#All],[Column1]]	Column1 in Table1 on Sheet1 in the active workbook, with table headers
Sheet1!Table1[[#All],[Column1]:[Column3]]	Columns 1, 2, 3 in Table1 on Sheet1 in the active workbook, with table headers
[Book.xlsx]Sheet1!A1:B2 '[Book One.xlsx]Sheet1'!A1:B2	Range A1:B2 on Sheet1 in the workbook Book. Book.xlsx must be located in the same destination as the automation file.
'C:\Documents\[Book.xlsx]Sheet1'!A1:B2	Range A1:B2 on Sheet1 in the workbook Book. Book.xlsx must be located in the specified file path location.

Use standard rules of range referencing in Excel, for example:

E.g. if names of worksheets or workbooks contain spaces, enclose the name in single quotes, e.g.'Orders April'!A1:H10, or '[All Orders]Orders April'!A1:H10.

Note:in order for automation commands to identify references, do not use spaces in the names of worksheets, workbooks, or tables.

Reformat:

This parameter defines which cells to reformat: cells with date format, time format, or date & time format.

Required parameter, cannot be blank

Options	Meaning
OnlyDate	Find only cells formatted as dates (e.g. "3/14/2012") and apply new format
OnlyTime	Find only cells formatted as time (e.g. "1:30 PM") and apply new format
DateAndTime	Find all cells formatted as date & time (e.g. "3/14/12 1:30PM") and apply new format

DateTimeFormat:

This parameter defines the new date or time format that will be applied to the cells.

Required parameter, cannot be blank

Options	Meaning
m/d/yyyy	Apply the date format m/d/yyyy, e.g. as in 3/14/2012
h:mm AM/PM	Apply the time format h:mm AM/PM, e.g. as in 1:30 PM
m/d/yyyy h:mm AM/PM	Apply the format m/d/yyyy h:mm AM/PM, e.g. as in 3/14/2012 1:30 PM

Using the standard Excel code, you can apply any custom date or time format.

Explore other automation commands

Any questions or suggestions?

How can we help?