Link to home
Start Free TrialLog in
Avatar of exp vg
exp vg

asked on

Excel Date Field - Inconsistent mm/dd/yy and mm/dd/yyyy

I have a date field where some of the entries are in the format

mm/dd/yy

And some are in mm/dd/yyyy

Please offer how I can get all of them to be the first format - mm/dd/yy. I have tried changing to short date but this does not work,

Thank you.
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

You  can run a simple macro to modify the data.
Just change this row Set rng = Range("A2:A15") to whatever your range is.
Sub Demo()

    Dim rng As Range, cell As Range

    Set rng = Range("A2:A15")

    For Each cell In rng
        cell.Value = Format(cell.Value, "mm/dd/yy")
    Next cell

End Sub

Open in new window

Avatar of exp vg
exp vg

ASKER

Thank you - is there a way to do this with a formula?
SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of exp vg

ASKER

Thank you.