I need to convert date strings in CSV to Date types when imported into Excel

Question

I am working on an iSeries platform. I have programs which will create a CSV for the data that I need, and an RPG program which will run a Java program to take the CSV data and insert it into a .xls template to create a new Excel spreadsheet (not .xlsx). The problem that I have is the data values in the CSV are in quotes and inserted into the XLS spreadsheet as text, which causes problems for my Date cells when it's all done. I get the "little green triangles" because Excel sees these as text entered into a Date cell.

I know I can use the DateValue() function, and it works great, but then the requirement would be that we delete the original column with the text data and I lose the reference for the DateValue() function. What I'm looking for (and I am still looking) is a way to load the text/string date value into the DateValue() function as it comes into the cell without needing to reference another cell, OR a way to permanently capture that value in a cell in a supplemental column so the original text column can be deleted. Original text column looks like "06/08/21" which has been updated to be "06/08/2021" to eliminate the "2-digit year" complaint from Excel. My supervisor is not opposed to putting the =DateValue() solution in play, but that requires a reference to the original cell (unless there's a way around that) and she wants the original deleted if we go that direction.

I would appreciate any help or direction on this. Including how I might ask this question better.

EDIT: Apparently I failed to communicate the issue clearly. The "customer" has a requirement that the values coming from the database be formatted properly in 5 columns. "Pure CSV" will not format correctly for dollar amounts in doing CL CPYTOIMPF from database file to the CSV. IF there's an alternative manner of getting data from the iSeries database into a CSV, or into an XLS file, I'm willing to read posts and articles to get it worked out.

Answer 1

You should be able to do a search and replace all for the quote marks and then reformat the cell as a date. You can record it as a macro and have a button in your toolbar linked to it, or run it as part of your import.

eg:

   Sub Datestring()
' Datestring Macro
' Removes quote marks and reformats as date
With Selection
    .Replace What:="""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    .NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"
    .EntireColumn.AutoFit
End With
End Sub

Just highlight the column you want to convert and run the macro, or just do it manually each time.