Find Cells Containing Date & Reformat DD-MMM-YYYY

[NigelG5]

Hi all. Hoping someone can assist me.

I am provided with large spreadsheets (that have been created from data held in Access) and have no control over the data provided.

In these sheets will be columns that contain dates in a variety of different formats and I need to create VBA that will look at the spreadsheet and for any cell containing a date, the format is changed to dd-mmm-yyyy (ie 01-Jan-2017) regardless of what it was originally. Up until now I have just manually changed this before sending the sheets on, but am looking to automate this.

I would like to avoid naming specific columns for this such as column D, G, H etc as depending on what data I am provided with, the column letters will change from sheet to sheet, so something that just looks for a date and then changes it to dd-mmm-yyyy.

Hoping some kind soul can offer some assistance.

Many thanks!

 

[mole999]

if its exported as a date, but the formatting is off, anything over 36526 (1/1/2000) onwards might work for you

the days are just a whole number, displayed as you wish

 

[MARK858]

If they are real dates and not text that looks like a date then try the slow code below.

Sub ChgDate()
    Dim sht As Worksheet, dCell As Range
    For Each sht In ActiveWorkbook.Worksheets
        For Each dCell In sht.UsedRange
            If IsDate(dCell) = True Then dCell.NumberFormat = "dd/mmm/yyyy"
        Next
    Next
End Sub

 

[NigelG5]

if its exported as a date, but the formatting is off, anything over 36526 (1/1/2000) onwards might work for you

the days are just a whole number, displayed as you wish

Thank you for that, but that solution wouldn't work in my case as there's lots of of other numerical values in the sheet and I guess there's a risk that could reformat other entries as dates, when they shouldn't be (plus there's dates going back into the 1960's in the sheets).

If they are real dates and not text that looks like a date then try the slow code below.

Sub ChgDate()
    Dim sht As Worksheet, dCell As Range
    For Each sht In ActiveWorkbook.Worksheets
        For Each dCell In sht.UsedRange
            If IsDate(dCell) = True Then dCell.NumberFormat = "dd/mmm/yyyy"
        Next
    Next
End Sub

Thanks Mark. That worked just as i needed it, and made a couple of slight adjustments as I only have one tab of data, so I used this one and works a treat.

Sub ChgDate()

Dim dCell As Range
    For Each dCell In ActiveSheet.UsedRange
    If IsDate(dCell) = True Then dCell.NumberFormat = "dd-mmm-yyyy"
    Next


End Sub