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:

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.

1. XLTools tab In the Date & Time group, open the drop-down list Click Unify date and time format.

   

   
2. Choose to find all cells that are Date.

3. Specify where to search:

   • Selected range
   • This worksheet
   • This workbook
   • All open workbooks

4. Select the preferred format:

   • Choose from preset formats, e.g. the customary in your country or language
   • Or set your custom date format

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

1. XLTools tab In the Date & Time group, open the drop-down list Click Unify date and time format.
2. Choose to find all cells that are Time.

3. Specify where to search:

   • Selected range
   • This worksheet
   • This workbook
   • All open workbooks

4. Select the preferred format:

   • Choose from preset formats, e.g. the customary in your country or language
   • Or set your custom time format

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

1. XLTools tab In the Date & Time group, open the drop-down list Click Unify date and time format.
2. Choose to find all cells that are Date & Time.

3. Specify where to search:

   • Selected range
   • This worksheet
   • This workbook
   • All open workbooks

4. Select the preferred format:

   • Choose from preset formats, e.g. the customary in your country or language
   • Or set your custom date & time format

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

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.