Excel date formatting issue on import

[Champak]

I have an excel sheet with a date/time column and the date in the background/formula bar that looks like "2/22/2007  6:01:43 AM". I formatted the cells to look like "02-22-2007 06:01:43". The problem is, despite how I formatted the cell, when I import the excel file into my listview it looks like "20070222060143". This causes a problem because when I go to export the file again, it will mess everything up and give me a character count issue in the cell and make the file so it can't be imported again.

How can I fix this? Why is the date cell messing up like this instead of importing it the way it is? Or is this simply how this would work for some reason?

Thanks

[Subz]

Just change the $iReturn of _Excel_RangeRead to 3 - The displayed text

_Excel_RangeRead($oWorkbook, Default, "A1", 3)

 

[Champak]

Thanks, but that doesn't work. I have an entire column...2000 lines...the help file says it only works for 1 cell. Which doesn't make sense to me since it's part of a function titled "range" .

[water]

That's an Excel limitation.
Maybe _DateTimeFormat helps to format the date to your liking?

Edited July 14, 2022 by water

[Champak]

yeah, I saw that. I wanted to avoid cycling through the entire array/listview to change how it looks, and then I have to bother with the "/", ":" and space character insertions to get what I want. So I don't think the date functions will work, I'll have to come up with something else. I'm just concerned of the overall time it will take. Oh well.

[water]

Let do Excel the translation (and assume that column A holds the date/time values):
I added a new column and inserted the following formula (German System): =TEXT(A1;"MM/TT/JJJJ HH:MM:SS". Then copied this formula to all cells in the new column based on the last cell in column A.
You now have the correctly formated date/time in this new column for all values in column A.
Use _Excel_RangeRead for this new column to get the date in the format you need.

Edited July 14, 2022 by water

[Subz]

You could also use _Excel_RangeCopyPaste and then convert it to an array, example:

$oRange = $oWorkbook.ActiveSheet.Range("A1:A2000")
$sRange = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oRange)
$aRange = StringSplit(StringStripWS(ClipGet(),3), @LF, 2) ;~ StringStripWS removes leading and trailing whitespace
_ArrayDisplay($aRange)

 

[Champak]

Thanks to both. I was already cycling through the array for something else, so I added the extra condition to convert the date as well. It doesn't seem to add too much extra time to the import.