convert date in yyyy/mm/dd format

**abraham30** · 12-11-2013, 02:35 PM

Hello,

In the attached sheet, I want a macro which will convert the date from dd-mon-yyyy to yyyy/mm/dd format for the two columns.

Can anyone help me.

Advance thanks

**Solus Rankin** · 12-11-2013, 02:38 PM

Why do you need a macro? Is there an issue with simply formatting those columns with the desired date format?

**abraham30** · 12-11-2013, 02:44 PM

Thanks for your quick response

In my project, i am using more than 20 macro to generate the output which I got from Oracle.
If a macro is developed to format the date in requested format, then it will be easier for the end user without using excel formula.

**Solus Rankin** · 12-11-2013, 02:53 PM

Range("A:A", "F:F").NumberFormat = "yyyy/dd/mm"

Are you asking that the dates seperated by commas be formatted also? Are they supposed to be separated into their own cells?

**abraham30** · 12-11-2013, 03:35 PM

Thanks solus for your help.
I forgot to add one more point. Please check the newly attached sheet once again where I have added date with time also.

How the macro will work and display only date (excluding time)

**Solus Rankin** · 12-11-2013, 09:14 PM

Sub test()
Dim rng As Range
Dim rngTotal As Range
Dim i As Integer

Set rngTotal = Union(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row), _
    Range("F1:F" & Range("F" & Rows.Count).End(xlUp).Row))

For Each rng In rngTotal
    If InStr(1, rng.Value, ";") > 0 Then
    i = InStr(1, rng.Value, ";")
        rng.Value = Format(Left(rng.Value, i - 1), "yyyy/dd/mm") & ", " & Format(Right(rng.Value, Len(rng.Value) - i), "yyyy/dd/mm")
    Else
        rng.NumberFormat = "yyyy/dd/mm"
    End If
Next rng
End Sub

**jindon** · 12-11-2013, 09:35 PM

Try this

Sub test()
    Dim e, a, i As Long, ii As Long, m As Object
    For Each e In Array("a", "f")
        With Range(e & 1, Range(e & Rows.Count).End(xlUp))
            a = .Value
            With CreateObject("VBScript.RegExp")
                .Global = True
                .Pattern = "\b(\d{2})\-[ADFJMNOS][a-z]{2}\-\d{4}\b"
                For i = 1 To UBound(a, 1)
                    If .test(a(i, 1)) Then
                        Set m = .Execute(a(i, 1))
                        For ii = m.Count - 1 To 0 Step -1
                            a(i, 1) = Application.Replace(a(i, 1), m(ii).firstindex + 1, _
                            m(ii).Length, Format$(CDate(m(ii)), "yyyy/mm/dd"))
                        Next
                    End If
                Next
            End With
            .Value = a
        End With
    Next
End Sub

**abraham30** · 12-12-2013, 05:35 AM

Thanks to both of you for looking into the matter.

Macro is working perfectly, as given by Solus.
Dear Jindon, the date is not being converted

**jindon** · 12-12-2013, 05:40 AM

Should work.

convert date in yyyy/mm/dd format

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

convert date in yyyy/mm/dd format

Re: convert date in yyyy/mm/dd format

Re: convert date in yyyy/mm/dd format

Re: convert date in yyyy/mm/dd format

Re: convert date in yyyy/mm/dd format

Re: convert date in yyyy/mm/dd format

Re: convert date in yyyy/mm/dd format

Re: convert date in yyyy/mm/dd format

Re: convert date in yyyy/mm/dd format

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Convert date format to (dd/mm/yyyy)

[SOLVED] Automatically convert Date format M/D/YYYY TO DD/MM/YY

[SOLVED] Convert Date Format from yyyy-mm-dd to dd mmm yy using VBA

[SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB

hot to convert a date object into a text (format yyyy-mm-dd)

Bookmarks

Bookmarks

Posting Permissions