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 viawebsiteoremail us.

Use with caution: beta features may contain some bugs and rough edges. To test the feature, take afree trialorupdateXLTools to version 5.3.1.

How to change date and time format across all Excel worksheets

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

How to change date format in Excel

  1. XLTools tab In the Date & Time group, open the drop-down list ClickUnify date and time format.
  2. Choose to find all cells that areDate.
  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
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

  1. XLTools tab In the Date & Time group, open the drop-down list ClickUnify date and time format.
  2. Choose to find all cells that areTime.
  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
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

  1. XLTools tab In the Date & Time group, open the drop-down list ClickUnify date and time format.
  2. Choose to find all cells that areDate & 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
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 selectFormat Cells On the Number tab, select Date (or Time) Apply necessary format Enter the date manually if necessary.

  • Or, use thePopup Calendar:

    Select cell Right-click and selectInsert 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?