Sign in to follow this  
Followers 0
ICANSEEYOU7687

Reading in cells from excel that are formated

7 posts in this topic

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

Share this post


Link to post
Share on other sites



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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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".


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Check this:

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.


I am just a hobby programmer, and nothing great to publish right now.

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