Jump to content

Excel date formatting issue on import


Recommended Posts

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

Link to post
Share on other sites

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" :huh2:.

Link to post
Share on other sites

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

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...