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