Champak Posted July 14, 2022 Share Posted July 14, 2022 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 comment Share on other sites More sharing options...
Subz Posted July 14, 2022 Share Posted July 14, 2022 Just change the $iReturn of _Excel_RangeRead to 3 - The displayed text _Excel_RangeRead($oWorkbook, Default, "A1", 3) Link to comment Share on other sites More sharing options...
Champak Posted July 14, 2022 Author Share Posted July 14, 2022 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" . Link to comment Share on other sites More sharing options...
water Posted July 14, 2022 Share Posted July 14, 2022 (edited) That's an Excel limitation. Maybe _DateTimeFormat helps to format the date to your liking? Edited July 14, 2022 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Champak Posted July 14, 2022 Author Share Posted July 14, 2022 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 comment Share on other sites More sharing options...
water Posted July 14, 2022 Share Posted July 14, 2022 (edited) 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Subz Posted July 14, 2022 Share Posted July 14, 2022 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) Link to comment Share on other sites More sharing options...
Champak Posted July 16, 2022 Author Share Posted July 16, 2022 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 comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now