User Profile
Nate__
Copper Contributor
Joined Oct 03, 2023
User Widgets
Recent Discussions
Re: Format all the dates in a workbook
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!22KViews1like0CommentsRe: 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.24KViews0likes2CommentsFormat 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.29KViews0likes4Comments
Recent Blog Articles
No content to show