Conversion from Excel time to labview time

[Ranjeet_Singh]

Hello all,

                                   I want to convert the excel time stamp to labview time stamp. Can you all please help me how to do this ?

In my application labview time is 05-11-12 14:56:00 and its equivalent excel time representation is 41218.6222222222.

I tried this formula,

Labview time =  24*3600*(Excel time - 1460) but anwer is wrong.

Thanks.

[PaulG.]

Did you search first?

[crossrulz]

Ok, here's what I found.  The conversion is:  Time Stamp = 86400*(Excel Time - 1462).  But here's the kicker: the timestamp is GMT.  You have to add your time zone offset as well.  I'm in the Eastern Time Zone, so I had to add 5 hours.  I'm not sure what DST will do to this.  I'm sure there's ways around it, I just don't have time to look into it more right now.

[Ranjeet_Singh]

Hi crossrulz,

                                     This logic is not working, Please try the number what i provided in the question.

Thanks.

[Ranjeet_Singh]

Hi all,

                            What i found is that when representing the time into excel sheet. the difference between time is not constant

#1. 14:51:00 11-05-2012    ------   41218.61875

#2. 14:52:00 11-05-2012    ------   41218.6194444444

#3. 14:53:00 11-05-2012    ------   41218.6201388889

#4. 14:54:00 11-05-2012    ------   41218.6208333333

#5. 14:55:00 11-05-2012    ------   41218.6215277778

#6. 14:56:00 11-05-2012    ------   41218.6222222222

So if the difference between the numbers is not same than we cannot find any common logic for conversion.

Agree friends ?

[crossrulz]

There are some slight rounding issues, but the results are fine for me...

NOTE: My date is setup as M/D/Y

[Ranjeet_Singh]

Its look like that i am committing some mistake. I also tried this but my answer is wrong. Can you please tell me why ?

One more thing how exactly you got this 1462, i think this should be 1460 ?

Thanks,

[crossrulz]

---

@Ranjeet_Singh wrote:

Its look like that i am committing some mistake. I also tried this but my answer is wrong. Can you please tell me why ?

One more thing how exactly you got this 1462, i think this should be 1460 ?

---

What time zone are you in? Can you attach the actual VI?

I put "1/1/1904 0:0:0" into and cell in Excel and then performed a value() on that cell.  The result was 1462.

[RavensFan]

---

@Ranjeet_Singh wrote:

Its look like that i am committing some mistake. I also tried this but my answer is wrong. Can you please tell me why ?

One more thing how exactly you got this 1462, i think this should be 1460 ?

Thanks,

---

Excel has two errors related to its data calculations.

1.  Day 0 is Jan 0, 1900.  Which of course doesn't exist.  Jan 1, 1900 is day 1 in Excel. (There is a 1 day error.)

2.  Excel thinks that Feb. 29, 1900 exists as a leap day, while in reality it doesn't.  (So there is another 1 day error.)

Day 0 is Jan 1, 1904 GMT in LabVIEW .  And since it doesn't have the year 1900, the presence of a leap day in 1900 or the lack thereof doesn't show up in LabVIEW's data calculations.

[JÞB]

another one.

Time to XL can be downloaded from here https://decibel.ni.com/content/docs/DOC-19689

as well as it's inverse XL to Time

Significantly Excel epoch is dependant on the OS system time zone (Yes, it is insane 0 means midnight on the 0th of January 1900 local time) and there is the famous leapyear bug Microsoft copied fron Lotus)

Don't forget to vote