Re : Re: Re : Re: trouble reading date from excel using report generator toolkit

[DavidT]

Hi, 
Im trying to read a date from an excel spreadsheet using the report generator toolkit. I've referred to this KB http://digital.ni.com/public.nsf/allkb/01B026BEB54E3414862571B600618827 and it works...sort of.

My dates are all reading 1 day and 4 years ahead. Can't really figure out why?

Any ideas would be appreciated!

Thanks,
David 

[James_R · Accepted solution]

The time format in LabVIEW is different from the time format in Excel. If you simply convert the timestamp cluster to a double
and paste that data into an Excel it wont make sense. This is because the Excel time format is based on the number of days
since 1 Jan 1900. The LabVIEW time is based on the number of seconds that have passed since 1 Jan 1904. This vi converts
 the LabVIEW time to Excel time. reverse the logic to go from excel to labview

[David_49]

Hello,

I'm very interested in having this vi, but with LV8.0.

May you convert it for me please ?

[James_R]

Message Edited by James R on 07-27-2009 11:13 AM

[David_49]

Thanks a lot !

There is a less of precision in the vi compared to the image attached about the value of 1461.79xxx

I think I have to take the longest ?

[PghFool]

If you add four years and a day's worth of seconds to the LabVIEW time BEFORE dividing by 86400, then you don't need to add a weird fraction afterwards.  Also, this conversion gives away why the two systems differ.  Microsoft made a mistake in their dating system by using the Julian calendar (a leap year every four years) instead of the Gregorian calendar (Julian, but no leap year if year is divisible by 100 but not 400), which has been the norm for about 300 years.  In other words, Excel thinks there was a February 29, 1900, but there actually wasn't.  LabVIEW correctly recognizes there isn't a February 29, 2100, either.

[RavensFan]

---

@PghFool wrote:

.....Microsoft made a mistake in their dating system by using the Julian calendar (a leap year every four years) instead of the Gregorian calendar (Julian, but no leap year if year is divisible by 100 but not 400), which has been the norm for about 300 years.  In other words, Excel thinks there was a February 29, 1900, but there actually wasn't.  LabVIEW correctly recognizes there isn't a February 29, 2100, either.

---

Microsoft didn't implement a Julian calendar.  If they did, they would have been off by 11 days.

They just didn't implement the leap year calculation correctly in the Gregorian calendar for the year 1900.

They also have an oddity that a value of 0 is January 0, 1900.

Excel does accurately calculate there is no leap year for the year 2100.  73109 is 2/28/2100 and 73110 shows as 3/1/2100