Sign in to follow this  
Followers 0
motormad

problem with date in array

4 posts in this topic

df
hello.

with the code

Global $aUitvD = _ExcelReadArray($oExcel, 1, 2,$iRows,1,0) ;Starting on the 2nd Column
_ArrayDisplay($aUitvD, "Starting on the 2nd Column")

i put dates from an excelfile in a array.

but the commmand '_ArrayDisplay' displays

[[0]|UitvD

[1]|20101010000000

[2]|20101011000000

[3]|20101012000000

[4]|20101013000000

in the excelfile the collom is

UitvD

10/10/2010

11/10/2010

12/10/2010

13/10/2010

How de i fix this?

thanx in advance

Share this post


Link to post
Share on other sites



motormad,

You need to do a bit of formatting - like this: :(

#include <Array.au3>

; Get your array from Excel as normal - we simulate it here
Global $aExcel_Input[5] = ["|UitvD", "|20101010000000", "|20101011000000", "|20101012000000", "|20101013000000"]

For $i = 0 To UBound($aExcel_Input) - 1
    ; Get rid of the |
    $sInput = StringtrimLeft($aExcel_Input[$i], 1)
    ; If the value is a date string then chop out the bits we want and rewrite it
    If Number($sInput) Then $sInput = StringMid($sInput, 7, 2) & "\" & StringMid($sInput, 5, 2) & "\" & StringMid($sInput, 1, 4)
    ; Save the formatted value back into the array
    $aExcel_Input[$i] = $sInput

Next

; Et voila, Robert est le frere de ta mere!
_ArrayDisplay($aExcel_Input)

All clear? :graduated:

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

All is verry clear

thanx :graduated:

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

It's also a way without formatting.

An date will stored in excel cell as an number. To get this number don't read out Cell.Value, that gives you only the string: YYYYMMDDhhmmss.

With Cell.Formula you get this number (distance from 1900/1/1 with Excel bug: 1900 as leapyear).

#include <Date.au3>
$oExcel = ObjCreate('Excel.Application')
$oExcel.Visible = 1
$oBook = $oExcel.WorkBooks.Open('C:\test\test.xls')

$iDateValue = $oBook.Sheets(1).Range('A1').Formula
$sDate = _DateAdd('D', $iDateValue -2, '1900/01/01') ; Excel has an leap year bug ==> 1900 will used as leap year, because that: one more day must deduct
ConsoleWrite($sDate & @CRLF)
Edited by BugFix

Best Regards BugFix  

Share this post


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
Sign in to follow this  
Followers 0