PDA

View Full Version : convert date/number



CJW_14
03-25-2019, 12:18 AM
Hi All,

Ive tried multiple methods to convert the attached list to date/numbers using VBA.

Tried numerous codes, each time Im able to convert some cells but not all.

Any help would be great!

rothstein
03-25-2019, 12:46 AM
I assume by "date/number" that you mean the serial date value for the given dates. If so, give this macro a try...



Sub DateToSerialDate()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:B" & LastRow) = Evaluate(Replace("IF({1},DATE(RIGHT(A1:A#,4),MID(A1:A#,4,2),LEFT(A1:A#,2)))", "#", LastRow))
End Sub



Note: Output is directed to Column B, if you want to overwrite the dates with their serial date value, then change the "B1:B" to "A1:A" (but test the code first before doing that to make sure it returns the values you want).

CJW_14
03-25-2019, 01:04 AM
Thanks for the reply, I was thinking I might need something along those lines. Essentially I wanted to then convert to dd/mm/yyyy using


For i = 1 To LastRow

Range("B" & i).Value = Format(Range("B" & i).Value, "DD/MM/YYYY")


Next i


but as you can see on the attached file it doesnt work on some rows, I cant figure it out.

rothstein
03-25-2019, 01:18 AM
Thanks for the reply, I was thinking I might need something along those lines. Essentially I wanted to then convert to dd/mm/yyyy

:confused: But the numbers are already in dd/mm/yyyy format... I am afraid I don't understand what you are trying to do.

CJW_14
03-25-2019, 01:23 AM
Thanks mate, Im actually using those dates for lookups and what Ive found is for whatever reason they appear to be formatted differently. So im trying ensure they are in the correct number/ date format. Maybe im missing something :)

rothstein
03-25-2019, 01:27 AM
Thanks mate, Im actually using those dates for lookups and what Ive found is for whatever reason they appear to be formatted differently. So im trying ensure they are in the correct number/ date format. Maybe im missing something :)
What do you mean by "they appear to be formatted differently"? Unless I am missing something, all of the dates appear to be formatted like you say you want them formatted. Can you give us an example where that is not the case?

大灰狼1976
03-25-2019, 01:51 AM
Hi CJW!
Something like below.
Remark: Because there are strings in cells, formatting directly doesn't work.

Sub test()
Dim arr, arrDate, i&
With Sheets("Source")
arr = .[a1].CurrentRegion
For i = 1 To UBound(arr)
arrDate = Split(arr(i, 1), "/")
arr(i, 1) = Join(Array(arrDate(2), arrDate(1), arrDate(0)), "/")
Next i
.[a1].Resize(i - 1) = arr
.Columns(1).NumberFormatLocal = "dd/mm/yyyy"
End With
End Sub

CJW_14
03-25-2019, 02:00 AM
All good :) maybe im confused then, I just dont understand why some dates (highlight in yellow) in the attached file from my previous post I cant format in dd/mm/yyyy. They appear in mm/dd/yyyy.

CJW_14
03-25-2019, 02:51 AM
Hi CJW!
Something like below.
Remark: Because there are strings in cells, formatting directly doesn't work.

Sub test()
Dim arr, arrDate, i&
With Sheets("Source")
arr = .[a1].CurrentRegion
For i = 1 To UBound(arr)
arrDate = Split(arr(i, 1), "/")
arr(i, 1) = Join(Array(arrDate(2), arrDate(1), arrDate(0)), "/")
Next i
.[a1].Resize(i - 1) = arr
.Columns(1).NumberFormatLocal = "dd/mm/yyyy"
End With
End Sub

Thanks alot, this appears to be working correctly! I had a play around and im just struggling to apply this code to column K in my data. Could you show me the difference please? :)

大灰狼1976
03-25-2019, 05:11 AM
Untested.

Sub test()
Dim arr, arrDate, i&
With Sheets("Source")
arr = .range("k1:k" & .[k65536].end(3).row)
For i = 1 To UBound(arr)
arrDate = Split(arr(i, 1), "/")
arr(i, 1) = Join(Array(arrDate(2), arrDate(1), arrDate(0)), "/")
Next i
.[k1].Resize(i - 1) = arr
.Columns(11).NumberFormatLocal = "dd/mm/yyyy"
End With
End Sub

Rob342
03-25-2019, 09:48 AM
I always use this to correct dates
1st make sure that your Excel language is not set to US
2nd you can format the columns to Date Req


Sub DateToSerialDate()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow

Range("B" & i).Value = CLng(CDate(Range("B" & i).Value))
NumberFormat = "DD/MM/YYYY"
Next i

End Sub

CJW_14
03-25-2019, 04:07 PM
Untested.

Sub test()
Dim arr, arrDate, i&
With Sheets("Source")
arr = .range("k1:k" & .[k65536].end(3).row)
For i = 1 To UBound(arr)
arrDate = Split(arr(i, 1), "/")
arr(i, 1) = Join(Array(arrDate(2), arrDate(1), arrDate(0)), "/")
Next i
.[k1].Resize(i - 1) = arr
.Columns(11).NumberFormatLocal = "dd/mm/yyyy"
End With
End Sub



Thanks, I thought I was changing the code correctly, its working perfectly. The only issue I'm having on another column is it appears blank cells between dates results in a run time error?

大灰狼1976
03-25-2019, 07:12 PM
Hi CJW!
Rob's method is more precise and can determine whether the date format is valid or not.
I suggest using his method.

rothstein
03-25-2019, 07:29 PM
I always use this to correct dates

Here is another way...



Sub ConvertDates()
Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, False, FieldInfo:=Array(1, xlDMYFormat)
End Sub

大灰狼1976
03-25-2019, 08:25 PM
@rothstein -- You surprise me every time.:thumb

CJW_14
03-25-2019, 08:25 PM
Thanks so much for your help everyone, all sorted, very supportive forum :)

CJW_14
03-25-2019, 08:27 PM
Here is another way...



Sub ConvertDates()
Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, False, FieldInfo:=Array(1, xlDMYFormat)
End Sub




This is also perfect!

rothstein
03-26-2019, 02:44 PM
@rothstein -- You surprise me every time.:thumb
I am not sure why that is so, but thank you.

snb
03-27-2019, 02:00 AM
Is that you Rick ?


Sub M_snb()
Columns(1).TextToColumns fieldinfo:=Array(1, 4)
End Sub

rothstein
03-27-2019, 08:03 AM
Is that you Rick ?


Sub M_snb()
Columns(1).TextToColumns fieldinfo:=Array(1, 4)
End Sub

Yes, it is me. How are you doing? By the way, the only reason I included all of the other parameters is from force of habit given that TextToColumns method remembers its settings from the last time it was evoked whether from VBA code or the Excel dialog box. I am not 100% sure, but I think that DataType is remembered which, if true, would mean your code line would be susceptible to failure if it was previously set to xlDelimited and the Other argument had been set to a slash (/) is a prior invocation.