Forum Discussion
Format all the dates in a workbook
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!