Forum Discussion
Format all the dates in a workbook
Hi Nate__,
to format all the dates in a workbook in Windows 11 without changing the default date format in the control panel can be done with Find and Replace method.
This method is quick and easy, and it does not require any knowledge of VBA. It is also the most reliable method, as it will format all of the dates in the workbook, regardless of where they are located.
To use the Find and Replace method:
- Open the workbook.
- Press Ctrl+H to open the Find and Replace dialog box.
- In the Find what field, enter [Date]. This will tell Excel to search for all cells that contain date values.
- In the Replace with field, enter the desired date format, in this case dd-mmm-yy.
- Click the Replace All button.
If you only need to format the dates in a single workbook, then the Find and Replace method is the easiest way to do it. However, if you need to format the dates in multiple workbooks or on a regular basis, then the macro method is a better option.
Note: If the dates in your workbook are stored as text, you will need to convert them to date values before you can format them. To do this, you can use the DATEVALUE() function.
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.
If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)
Hi LeonPavesic ,
I appreciate you taking the time to try to answer my question. Your solution sort of worked.
I was able to use the Find & Replace function, but not quite in the way you suggested. I had to use "select a format from cell" for both find and replace, but leave the text fields completely blank. I manually changed one of the dates to the correct format and used it as the replace format. Even using this method, it still took several tries of selecting random date cells in order to get anything to change. Sometimes the dialogue box would say there were no search results, sometimes it would say it had made 100+ replacements, but none of the cells had changed. Sometimes a few of them would change, but not all. I'm not sure if there were just small differences in cell format that messed things up, or if it was another issue.
Not a perfect solution, but works fine in the short term. Wish there was a way to set the default date format for a workbook.
Hi Nate__ ,
thanks for your update.
Sorry to hear that my solution didn't work as expected.
If you need to format dates in multiple workbooks or on a regular basis, I recommend using a macro.Here is a simple macro that you can use to format all of the dates in a workbook:
Sub FormatDates() 'Select all cells in the workbook Range("A1:XFD1048576").Select 'Apply the date format Selection.NumberFormat = "dd-mmm-yy" End SubTo use this macro, simply press Alt+F11 to open the Visual Basic Editor. Then, paste the macro code into a new module.<br /><br />To run the macro, press F5. The macro will format all of the dates in the workbook to the dd-mmm-yy format.
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)LeonPavesic , thanks for the response. Again, didn't quite work as expected. I've never used VBA before, so it took me a while to figure out what was going on. The issue with your script was that it changed the format of every cell to date, including the empty ones and anything with text or a number.
After a few tries, here's what I found that worked:
Sub ChangeDateFormat()
Dim cell As Range
' Loop through each cell in the active sheet
For Each cell In ActiveSheet.UsedRange
' Check if the cell is not empty and contains a date
If IsDate(cell.Value) Then
' Change the date format to dd-mmm-yy
cell.NumberFormat = "dd-mmm-yy"
ElseIf IsEmpty(cell.Value) Then
' Apply general formatting when the cell is empty
cell.NumberFormat = "General"
End If
Next cell
End SubI don't really know what I'm doing, but the above has worked well so far as a way to modify the default date format without messing with anything else.
Thanks!