Forum Discussion
Format all the dates in a workbook
I have a sheet with a mixture of dates and numbers, I want to change the date format to dd-mmm-yy for all the dates. The dates are not in uniform columns, so I would have to select all of them individually to change them manually. I know I can change the defualt format in the control panel, but I would really like to avoid that. I don't want to change my whole system, just this workbook. I tried conditional formatting, but the dates go beyond last month and next month so that was not a great solution.
Is there a way to do this or am I just out of luck?
Using windows 11.
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)
