Date Format Change

[Abhijeet]

Hi

I have Data in Excel from that i want change which cells contain Dates that dates format i want to change to US Date format (NumberFormat = "[$-409]dd/mmm/yy;@")
i mention in D1 cell expected Date Format please tell me how to do this in vba

 

[Marc L]

Hi !

It seems just need a cell format accordingly and w/o any code …

​

 

[Abhijeet]

Hi Marc L

In Data Where ever Date is mention that cell i want to change Date Format Expected Foemat is US Date Format mention in d1 cell

 

[Marc L]

Just apply the cell format …

And if you really want code, use Macro recorder.

​

 

[Abhijeet]

Data is huge so manual do this time consuming thats why i need Macro for this

 

[vletm]

Something like ...

Sub US_Date_Format()
    Application.ScreenUpdating = False
    With ActiveSheet
        y_max = .UsedRange.Rows.Count
        x_max = .UsedRange.Columns.Count
        For y = 1 To y_max
            For x = 1 To x_max
                If IsDate(.Cells(y, x)) Then .Cells(y, x).NumberFormat = "[$-409]dd/mmm/yy;@"
            Next x
        Next y
    End With
    Application.ScreenUpdating = True
End Sub

 

[Abhijeet]

Thanks Vletm Its work Perfect

 

[Deepak]

Short version of the said approach!

Sub d_change()
Dim r As Range
For Each r In Sheet1.UsedRange
    If IsDate(r) Then r.NumberFormat = "[$-409]dd/mmm/yy;@"
Next
End Sub

 

[Abhijeet]

Short version of the said approach!

Sub d_change()
Dim r As Range
For Each r In Sheet1.UsedRange
    If IsDate(r) Then r.NumberFormat = "[$-409]dd/mmm/yy;@"
Next
End Sub

Hi Deepak

After Run This macro then on my Laptop its cell format show US Date Format
but i run in my office then cell format show Custom Date format why is this difference what can i do i want run this in my office

 

[Deepak]

It's due to regional setting in both PC are different.

 

[Abhijeet]

Hi

I think i do not have access to change setting in Office is any other way to do that task in Macro

It's due to regional setting in both PC are different.

 

[Marc L]

Hi !

It's not settings in Office but Regional settings in Windows ‼

Anyway, try with year upon four digits …
​

 

[Abhijeet]

Hi

I know its not in Microsoft Office setting i mean to say Office means my Company & also i tried in year upon four digits its also show in Custom Date format in cell

 

[Abhijeet]

Hi

It is possible select date range then right click go to format cell select date then US Format with help of send keys in vba

 

[Nitesh Khot]

Hi Abhijeet..!

This will not work if Regional setting is different...First time when i got some data I am unable to change format using =text() or format cell ....You have to change Regional Setting ( same as your home pc) from Control Panel...

 

[Abhijeet]

Hi

I change this line Then r.NumberFormat = "[$-409]dd-mmm-yy;@"
then work no need to change any setting

 

[Nitesh Khot]

This is not working on myside...using 2007 win7...date format on my windows is DD-MM-YYYY and i want to convert date (MM/DD/YYYY HH:MM:SS) into
"YYYY-MM-DD HH:MM:SS" but unable to chnage like this...what can i do?