How to check a specific format using regex in VBA

Question

I have dates in my excel file in the following format MM/DD/YYYY

I am going through worksheet using for each loop and getting value by cell.Value

I'd like to check whether currently processed cell is in the above format and if so, change it to YYYY-MM-DD ( database format )

Can I do this in VBA ?

Thanks in advance,

Those are real dates or dates as strings? I asked this because Excel treats dates as numbers so how you see a date in a cell is due to the formatting while there is a number underneath the date. To check if the date is real date not a date string, assuming you have date in A2, then in any blank cell type the formula =ISNUMBER(A2). A2 contains a real date if the formula returns True else it is a date string. — Subodh Tiwari sktneer
– Subodh Tiwari sktneer, Commented Apr 21, 2018 at 14:16

Gary's Student · Accepted Answer · 2018-04-21 14:36:34Z

1

Without Regex:

Sub DateTest()
    With ActiveCell
        If IsDate(.Value) And .NumberFormat = "mm/dd/yyyy" Then
            .NumberFormat = "yyyy-mm-dd"
        End If
    End With
End Sub

NOTE:

If the values are Strings, changing the NumberFormat will have no visible effect on the cells.

edited Apr 21, 2018 at 14:36

answered Apr 21, 2018 at 14:22

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PeterT · Accepted Answer · 2018-04-21 14:22:43Z

0

You don't need regex

Option Explicit

Sub ChangeDateFormats()
    Dim thisCell As Range
    For Each thisCell In ActiveSheet.UsedRange
        Debug.Print thisCell.NumberFormat
        If thisCell.NumberFormat = "m/d/yyyy" Then
            thisCell.NumberFormat = "yyyy-mm-dd"
        End If
    Next thisCell
End Sub

answered Apr 21, 2018 at 14:22

PeterT

8,5971 gold badge19 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to check a specific format using regex in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related