Beta

This is a new feature and we can’t wait to hear your feedback. Is it useful for your tasks? Is it easy to use? Just let us know via website or email us.

Use with caution: beta features may contain some bugs and rough edges. To test the feature, take a free trial or update XLTools.

How to change date and time format across all Excel worksheets

How to change date and time format in Excel

Changing date and time format in Excel can be tricky. Sometimes you end up with a spreadsheet where dates display in multiple different formats, e.g. 2020-08-03, 4-Aug-20, 8/5/20, etc. How can you get these dates to display uniformly across worksheets, e.g. in the date format dd/mm/yyyy?

The Unify Date Format feature will help find all date or time cells, and change their format at once:

Change date cells only, time cells only, or date & time cells
Unify format across ranges, worksheets and workbooks
Choose from preset format options or set any custom format

Before you begin, add Unify Date Format to Excel

Unify Date Format 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.

How to change date format in Excel

XLTools tab In the Date & Time group, open the drop-down list Click Unify date and time format.
Choose to find all cells that are Date.
Specify where to search:
- Selected range
- This worksheet
- This workbook
- All open workbooks
Select the preferred format:
- Choose from preset formats, e.g. the customary in your country or language
- Or set your custom date format

Tip: the preview at the bottom gives an example of how dates will display in cells. The preview uses the current date as an example.

5. Click OK Done! All date cells have the same format now.

How to change time format in Excel

XLTools tab In the Date & Time group, open the drop-down list Click Unify date and time format.
Choose to find all cells that are Time.
Specify where to search:
- Selected range
- This worksheet
- This workbook
- All open workbooks
Select the preferred format:
- Choose from preset formats, e.g. the customary in your country or language
- Or set your custom time format

Tip: the preview at the bottom gives an example of how time will display in cells. The preview uses the current time as an example.

5. Click OK Done! All time cells have the same format now.

How to change date & time format in Excel

XLTools tab In the Date & Time group, open the drop-down list Click Unify date and time format.
Choose to find all cells that are Date & Time.
Specify where to search:
- Selected range
- This worksheet
- This workbook
- All open workbooks
Select the preferred format:
- Choose from preset formats, e.g. the customary in your country or language
- Or set your custom date & time format

Tip: the preview at the bottom gives an example of how date & time will display in cells. The preview uses the current date and time as an example.

5. Click OK Done! All cells with date & time values have the same format now.

If date format does not change

The Unify Date Format feature will only process cells which are formatted as date or time. If some cells remain unchanged, it means that these cells are not recognized by Excel as date or time. Most likely these cells have general, text, or number format.

Then you need to apply date format to the unrecognized cells:

Apply date format manually:

Select cell Right-click and select Format Cells On the Number tab, select Date (or Time) Apply necessary format Enter the date manually if necessary.
Or, use the Popup Calendar:

Select cell Right-click and select Insert Date Select the required date from the date picker The date will be inserted, and the cell will get date format automatically.

Any questions or suggestions?

How can we help?