• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

correction of date format

Tom22

Member
Hi,


I am looking for a solution, where i can convert numbers into date format.



On monthly basis i received a file where date is given as 20201231. So which meand it is 31st dec 2020.


But i need this to be 2020/12/31 in this format.


So data is very huge...it is around 50-60k rows and date is like earlier format only and date is different in every row. Like 1st row may have 20201231 and next row could be 20200725.


How can i do that?
 
Code:
Sub Datey()

Dim Rng As Range
Dim SubRng As Range
Dim LstRow As String
Dim SubYear As Integer
Dim SubMonth As Integer
Dim SubDay As Integer

LstRow = Cells(Rows.Count, "A").End(xlUp).Row 'find last non empty row in column A
Set Rng = Range("A2:A" & LstRow) 'set the range to be from A2 to whatever the last non empty cell is in same column

For Each SubRng In Rng
    SubYear = Left(SubRng, 4)
    SubMonth = Mid(SubRng, 5, 2)
    SubDay = Right(SubRng, 2)
    SubRng = DateSerial(SubYear, SubMonth, SubDay)
Next SubRng

Rng.NumberFormat = "DD MMM YYYY"

End Sub
 
Thanks Chirayu,


It is done almost.


Attaching one file, col.A is what i got on monthly basis


Col. B what i am getting after running code, slight change has been made.


Col. C this is what i am looking for.

Can you please take a look into the same
 

Attachments

  • gg.xlsm
    65.1 KB · Views: 12
Hi,​
as this thread is located in the Excel formulas forum :​
  • first set the cell C2 as the desired date format

  • Enter this beginner level formula in this cell : =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

  • Copy down, that's it ! …
 
Another way for big data and still at very beginner level (I saw children well used it) :​
select all the dates then call the easy Excel convert or text to columns feature​
then well answer to its assistant in particular for the date format.​
Once it works, reload the initial workbook, activate the Macro Recorder and redo the same operations :​
you will get your own code base (a single codeline is necessary) !​
If you need further help for a code, see in the appropriate VBA forum rather than here …​
 
takes value in col A - converts to date + format in col B

Code:
Sub Datey()

Dim Rng As Range
Dim SubRng As Range
Dim LstRow As String
Dim SubYear As Integer
Dim SubMonth As Integer
Dim SubDay As Integer

LstRow = Cells(Rows.Count, "A").End(xlUp).Row 'find last non empty row in column A
Set Rng = Range("A2:A" & LstRow) 'set the range to be from A2 to whatever the last non empty cell is in same column

For Each SubRng In Rng
    SubYear = Left(SubRng, 4)
    SubMonth = Mid(SubRng, 5, 2)
    SubDay = Right(SubRng, 2)
    SubRng.Offset(0, 1) = DateSerial(SubYear, SubMonth, SubDay)
Next SubRng

Range("B2:B" & LstRow).NumberFormat = "YYYY/MM/DD"

End Sub
 
Last edited:
Back
Top