LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Converting formatted excel date to useful data in labview

I am using the report generation toolkit for Office, and reading an excel file.  All the data comes back fine, except for the date column.  In excel the column is formatted as a date (ex. 5/5/05) but when it comes through in labview it ends up being something like "37740", it's the same effect if you switch the format in excel to general, the date changes to a number.  Is there anyway to leave the formatting the way it is in Excel and still bring into labview a useful date, or would I have to format the excel file differently?
 
Thanks.
0 Kudos
Message 1 of 7
(3,051 Views)
I think I have it figured out using the Excel serial number date.  I attached a vi of something I threw together quickly to convert excel date serial numbers to labview date info.  I'm not 100% if it works all the time, but the few examples I tried worked fine.
0 Kudos
Message 2 of 7
(3,046 Views)
You can take a look at the tools posted on LAVA Forum by jbrohan. I didn't try them myself. Usual disclaimer.


LabVIEW, C'est LabVIEW

0 Kudos
Message 3 of 7
(3,041 Views)

Can you explain this conversion?

0 Kudos
Message 4 of 7
(768 Views)

Hi snehamadhu,

 

welcome to the LabVIEW board!

 


@snehamadhu99 wrote:

Can you explain this conversion?


Excel counts "time" with unit "days", while LabVIEW measures time in seconds.

Excel uses a different epoch than LabVIEW, with "epoch" being the start of the time scale.

They both handle timezones differently.

 

The VI tries to convert from one value to the other:

  • it's very old, using deprecated functions
  • it assumes a fixed timezone, which makes it buggy when used world-wide

You will find more recent implementations to solve the same problem in the LabVIEW board!

 

Best regards,
GerdW


using LV2016/2019/2021 on Win10/11+cRIO, TestStand2016/2019
0 Kudos
Message 5 of 7
(761 Views)

why is it multiplied by 2 in the conversion part of the code?

0 Kudos
Message 6 of 7
(714 Views)

Hi snehamadhu,

 


@snehamadhu99 wrote:

why is it multiplied by 2 in the conversion part of the code?


As I wrote before: the code assumes a fixed timezone…

Best regards,
GerdW


using LV2016/2019/2021 on Win10/11+cRIO, TestStand2016/2019
0 Kudos
Message 7 of 7
(709 Views)