DateTime from Excel Cell ExcelApplication MSOfficeAutomation

Targets Windows
1

hello,
how to read a DateTime from a Excel Cell?
And its possible to read a formula from Cell?

at least Cells(row,x).Text gave me a date string
but i like to read direct the date cell without converting.

2
1 Like
3

oha i overlooked this property, thanks

'Var formula As String = sheet.Range(“J3”).Formula
Var formula As String = sheet.Cells(3,10).Formula
System.DebugLog formula

4

still a probem with date
in excel it is 02.01.2020
Format Cells show type *14.03.2012
Locale German (Germany)

here i become a error message “_VariantDouble cannot be cast to DateTime”

Var value As DateTime = sheet.Range("K3").Value
System.DebugLog value.ToString

with .Value.DateValue i got
No automation server present

with Var value As DateTime = sheet.Range(“K3”).DateValue i got
Unknown name, (failed on “DateValue”)

so for me it means i need to cast the double into a valid xojo datetime.
if me do this

Var value As DateTime = new DateTime(sheet.Range("K3").Value, TimeZone.Current)
System.DebugLog value.ToString

02.01.2086, 01:00:01

Var valueStr As String = sheet.Range("K3").Text
System.DebugLog valueStr

show 02.01.2020

5

Hi Markus,

I believe there are different ways that time is formatted in Excel compared to Xojo.

Excel saves a data and time as a serial number such as (42441.482088). The numbers to the left of the decimal point are the number of days since 1 January 1900, and the numbers to the right is the percent of the day which has passed (48.2088 % of the day has passed).

There is more information in the following forum link: Excel date number

Happy to help

1 Like
6

ah,ok,interesting,thanks