Date Format Conversion Question

Rosstamon

Board Regular
Joined
Sep 12, 2007
Messages
67
I regularly download a .csv file which I open in Excel and then save as 97-2004 workbook (.xls). Contained in this .csv file is a column with dates which are formatted as short dates. I can't seem to find a way to convert these to long dates other than changing the format and then manually retyping each date, perhaps there is one more step I'm unaware of that will accomplish this. Can someone help me out? Would very much appreciate it.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,425
Office Version
2019
Platform
Windows
Here is a VBA solution for you

VBA Code:
Option Explicit

Sub Dates()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    'change the above line to your column holding the dates and each line below 
    For i = 1 To lr
        Range("A" & i) = CDate(Range("A" & i))
        Range("A" & i) = Format(Range("A" & i), "Long Date")
    Next i
End Sub
 

Rosstamon

Board Regular
Joined
Sep 12, 2007
Messages
67
Here is a VBA solution for you

VBA Code:
Option Explicit

Sub Dates()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    'change the above line to your column holding the dates and each line below
    For i = 1 To lr
        Range("A" & i) = CDate(Range("A" & i))
        Range("A" & i) = Format(Range("A" & i), "Long Date")
    Next i
End Sub

Thank you Alan, unfortunately I'm not familiar with how to do VBA stuff. I realize I should learn, but every time I try I usually give up and just continue to do things the hard way. How difficult would it be for me to learn this?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,425
Office Version
2019
Platform
Windows
Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

Here is a link to tutorials Learn Excel VBA - Google Search
 

Watch MrExcel Video

Forum statistics

Threads
1,095,687
Messages
5,445,952
Members
405,372
Latest member
Vithanalas

This Week's Hot Topics

Top