Reading in cells from excel that are formated

[ICANSEEYOU7687]

This is like my third post today and im sorry! I hate to be annoying...

But now I am pulling in data from an excel sheet and the data im looking at is time. For example the time in the sheet says 1:00:00 PM, but since this is set to a time format, this is not its actual value. If you change the cell from time to "general" the value changes to 0.58777777 (or something like that)

Does anyone have any experience with this? What I would really like to do is simply change the 1:00:00 PM to military time (So this would be 13:00), but by changing the format options, this only changes the way the cells are seen, but not the value that autoit pulls.

Thanks!

-Wesley

[water]

Time in Excel is stored as a number between 0 (00:00:00) and 1 (23:59:59). How do you retrieve the value of a cell? Do you use the Excel UDF?

[ICANSEEYOU7687]

Here is the very generalized code i was trying to run. I have a lot more, but I just grabbed the parts that I thought were relevant

#include <Excel.au3>

Global $oXLC_Excel = _ExcelBookOpen("test.xls")
Local $aArray = _ExcelReadSheetToArray($oXLC_Excel)  ;
$Cell = $aArray[2][5]
MsgBox(0, "Cell", $Cell)

Thanks for your quick response!

[water]

The functions in the UDF use the Value property to return the data. You need the Text property.

I would suggest to create your own function "_ExcelReadSheetToArrayText" using a copy of "_ExcelReadSheetToArray" and replace ".Value" with ".Text".

[ICANSEEYOU7687]

That seemed to work!

Now if I pull out these values as text, and I for example take out two numbers 13:00 and 12:00 and I strip out the 13 and 12 and tried to subtract them will this cause issues if they are not considered integers?

Thanks!

[water]

No problem. AutoIt converts text to numbers before doing arithmetic.

Check this:

ConsoleWrite("13" - "12" & @CRLF)

[Myicq]

That seemed to work!

Basically you have, if I remember correctly:

• Value = actual value. For a time, 0..1.
• Text = formatted result. Here, 13:05:23 fx
• Formula = the actual formula that produced the value.

So essentially you can get all "layers" from an Excel cell.

Now if I pull out these values as text, and I for example take out two numbers 13:00 and 12:00 and I strip out the 13 and 12 and tried to subtract them will this cause issues if they are not considered integers?

You could just as easily have subtracted 0.6 from 0.5 and used the 0.1 to caculate to 10% of 24 hours. That's what Excel does internally. But see above also.