seres Posted April 11, 2016 Share Posted April 11, 2016 hello, im having trouble reading dates from excel, for example, i have a column with date 07/04/2015 and it reads it like 20150407000000, is there a way to read it as it is, i have tried excel rangeread with option 3 but it returns me nothing expandcollapse popup#include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> Global $Sheet1_ARRAY, $Sheet2_ARRAY, $Master_ARRAY Local $oExcel = _Excel_Open(0) $oExcel1 = _Excel_BookOpen($oExcel, @ScriptDir&"\date.xlsx") $oExcel0 = _Excel_BookOpen($oExcel, @ScriptDir&"\Master.xls") $Sheet1_ARRAY = _Excel_RangeRead($oExcel1, Default, Default, ) Global $FinalARRAY[9000][500]; is there a better way to set the values for the array? Local $iRowsSheet1 = UBound($Sheet1_ARRAY, $UBOUND_ROWS) ; Total number of rows filas. Local $iColsSheet1 = UBound($Sheet1_ARRAY, $UBOUND_COLUMNS) ; Total number of columns. $inc = 0 For $columna = 0 To $iColsSheet1 - 1 if $Sheet1_ARRAY[0][$columna] = "DATE_APLI" Then For $j = 0 To $iRowsSheet1 - 1 $clasvalue = $Sheet1_ARRAY[$j][$columna] $FinalARRAY[$inc][$columna] = $clasvalue $inc +=1 Next $inc = 0 EndIf Next _Excel_RangeWrite($oExcel0, Default , $FinalARRAY) _Excel_BookSave($oExcel0) _Excel_BookClose($oExcel0) _Excel_BookClose($oExcel1) Link to comment Share on other sites More sharing options...
water Posted April 11, 2016 Share Posted April 11, 2016 That's a limitation of Excel. You can only retrieve a single cell with option 3. Translate the date to the needed format using a RegExp or the String* functions. 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...
JailDoctor Posted July 31, 2016 Share Posted July 31, 2016 You may simply trim your array like this: _ArrayTrim($YourDate,6,1) Link to comment Share on other sites More sharing options...
AutoBert Posted July 31, 2016 Share Posted July 31, 2016 (edited) or loop throug array calling this func: Edited July 31, 2016 by AutoBert Link to comment Share on other sites More sharing options...
BlackLumiere Posted January 27, 2020 Share Posted January 27, 2020 Here is an example of how to use some String properties. You can save the values in a variable, eliminate the slashes if you dont need them, etc. $Date="20150407000000" $DIV=($Date)/(10^6) ;Division: To make easier to extract $Day and in case you want to use that inverse date $Year = StringLeft($DIV,4) $Month = StringMid($DIV,5,2) $Day = StringRight($DIV,2) MsgBox(0, "",$Day & "/" & $Month & "/" & $Year) •You can re-order the format to match with American dates "mm/dd/yyyy", or non tradicional formats, which is your case "dd/mm/yyyy". Pos: I know is late, but just posted as an alternative to handle this issues with excel dates. Regards! Link to comment Share on other sites More sharing options...
BlackLumiere Posted January 27, 2020 Share Posted January 27, 2020 Here is another example of how to use some String properties. In this case the Input value is a short date from excel and the output can be: For $Format equal to: 0: Same Format without slashes 1: International Format "dd/mm/yyyy" 2: American Format "mm/dd/yyyy" Note: If u are a begginer, to use the function u just need to write in any part of your code: ExcelDate( $YourVariableWithDate , 0 or 1 or 2) Example: Copy the code below #include <MsgBoxConstants.au3> #include <Date.au3> Func ExcelDate($Date,$Format) ;0: Same Format ;1: International Format ;2: American Format $DIV=($Date)/(10^6) $Year = StringLeft($DIV,4) $Month = StringMid($DIV,5,2) $Day = StringRight($DIV,2) Select case $Format = "" or $Format = 0 $Date = $Day & $Month & $Year case $Format = 1 $Date = $Day & "/" & $Month & "/" & $Year case $Format = 2 $Date = $Month & "/" & $Day & "/" & $Year case $Format = 3 $Date = $Year & $Month & $Day EndSelect Return $Date endfunc Local $RawDate = "20150407000000" Local $Example=ExcelDate($RawDate,3) MsgBox(0, "",$Example) Pos: Again, I know is late, but just posted as an alternative to handle this issues with excel dates. Regards! 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