Macro for DATEVALUE

swingline · Nov 10, 2019

I have software that exports date data to a workbook. I am expected to utilize this data to feed another report. The issue is all of the dates are exported into text, I have to update this weekly sometimes ad hoc when requested. I was wondering if I could modify my macro to somehow get around the DATEVALE limit of one column at a time. So say select a range like E13:Y66 then convert them all at once?

Code:

Sub convertdate()
'
' convertdate Macro
' convert to dates
'

'
    Selection.TextToColumns DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Selection.NumberFormat = "[$-409]d-mmm-yyyy;@"
End Sub

jasonb75 · Nov 10, 2019

Try copying an empty cell, then paste to your table of dates using PasteSpecial > Add.

I think it should work with your dates if text to columns is working, if it does then you should be able to get the code that you need from the macro recorder.

Other than that, you could run text to columns on a loop instead of running each column manually.

swingline · Nov 10, 2019

jasonb75 said:
Try copying an empty cell, then paste to your table of dates using PasteSpecial > Add.

I think it should work with your dates if text to columns is working, if it does then you should be able to get the code that you need from the macro recorder.

Other than that, you could run text to columns on a loop instead of running each column manually.

Thanks for the reply that did convert to a number, although I lose cell formating that way. At least I have a better starting point.

**EDIT**
Actually I ended up trying this and it works great for my needs

Code:

Sub convertdate()
'
' convertdate Macro
' convert to dates
'

'
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With
    Selection.NumberFormat = "[$-409]d-mmm-yyyy;@"
End Sub

jasonb75 · Nov 10, 2019

If you're simply trying to get around the problem of selecting a column, running the macro, selecting the next column running it again, etc. Then try this with the whole range selected.

Code:

Sub convertdate()
'
' convertdate Macro
' convert to dates

Dim Col As Range
    For Each Col In Selection.Columns
    Col.TextToColumns DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Col.NumberFormat = "[$-409]d-mmm-yyyy;@"
Next Col
End Sub

Macro for DATEVALUE

swingline

New Member

Excel Facts

jasonb75

Well-known Member

swingline

New Member

jasonb75

Well-known Member

Similar threads

Forum statistics

Share this page

Macro for DATEVALUE

swingline

New Member

Excel Facts

jasonb75

Well-known Member

swingline

New Member

jasonb75

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock