excel range read date problem

[seres]

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)

 

[water]

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. 

[JailDoctor]

You may simply trim your array like this:

_ArrayTrim($YourDate,6,1)

 

[AutoBert]

or loop throug array calling this func:

 

Edited July 31, 2016 by AutoBert

[BlackLumiere]

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!

[BlackLumiere]

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!