I need a formula to convert a date into an integer (for use in Lookout)

[smigman]

I have a DATA logger with the following:

40001 = 4 (2004)
40002 = 2 (February)
40003 = 5 (5th)
40004 = 13 (1:00pm)
40005 = 12 (12 minutes)

Lookout requires a date that it understands (eg. January 1, 1900).

All I need is a basic formula to convert the date and time.

This formula does not need to be specifically written for Lookout. Just a basic formula that I can do on paper or a calculator.

I can integrate that formula into the Lookout software myself.

[Khalid]

Hello Smigman,

First of all, I apologize in advance for not giving you "just the formula." And for the lengthy explanation (had to wait till after work hours), which you are very likely aware of already. I am writing this response in much detail so that it may benefit others.. hopefully 🙂 And so that we understand the underlying principle involved, which will hopefully help us in building the formula the best way that suits us.

As you have figured out, the data and time in Lookout is represented as a real number. This real number's integer portion represents days, and the decimal portion denotes time. The integer portion is basically the number of days since 01/01/1900. And the decimal portion is the number of seconds since midnight.

So, for instance, if you insert the today() Expression in Lookout, you'll get a integer 38022 (which corresponds to today, Feb. 5th, 2004). In other words, today is the 38022nd day since 01/01/1900. Tomorrow, Feb. 6th 2004, will be 38023 and so on.

The decimal part denotes time. A day has 24*60*60 = 86400 seconds, obviously. So, 1/86400 = 1.15741E-5 roughly represents one second. For instance, 38022.00001157 will give 02/05/2004 00:00:01.

Coming to the formula now, for Time, first convert it to total seconds from midnight. E.g., 5:15:07pm would be (17*60*60) + (15*60) + 7 = 62107 seconds total since midnight. To get the Lookout's decimal part, divide this by 86400.

62107/86400 = 0.71883102

Therefore, 38022.71883102 would now give 02/05/2004 17:15:07. Computing Time is relatively easy.

For the Date -- which is more complicated-- you could keep track of the total number of days either from 01/01/1900, or better still, a more recent day, like say 12/31/2003, which corresponds to 37986. To this reference you will keep adding 1 for each additional day to get the number for the current day. Note, you will have to accomodate leap years (Feb. 29th of this year, for instance).

It's very helpful to have the reference day as the last day of the past year. That can be derived by counting the number of days since 01/01/1900 as follows:

104 years * 365 days = 37960;
+ 1 day for each leap year

A leap year is a year divisible by 4 (and 400 if the year ends with two zeros). There were 26 leap years from 1900 till 2003.

So, 37960 + 26 = 37986. 12/31/2003 is thus represented by 37986.

To get the integer for the Current Date we would first find what day of the year it is. Then add it to the reference day. Feb 5th, is the 36th day of the year. Adding this to 37986, gets us 38022.

In your case you will have to come up with the correct day of the year using registers 40002 and 40003. Not sure if this helped or confused you more.

I tried 🙂

Khalid

[smigman]

Yes, you have been a great help.

All of these little bits and pieces, that you have answered in the recent days, have actually help a tremendous amount.

Now figuring the days of each month is another hassle. Not all months are the same.

Because of your help, I can view a small CSV file in HyperTrend, with all of the features. Now I may be able to apply it to a huge CSV file, with 2,880 points.

You may wonder "how can that be"? Well, I have to open the LKS file for editing and sequentially duplicate something like this (2,880 times):

Logger1.time1 = Table1.B1;
Logger1.time1 = Table1.B2;
Logger1.time1 = Table1.B3;
Logger1.time1 = Table1.B4;
...and so on.

I think you know what I mean.

Fortunately, I have VB skills and can write a "Do Loop" p
rogram that will write it for me in less than a few seconds, then I can paste it in..

There are 2,880 15-minute segments in 30 days. Lookout sees "0" or null points as zero. So when the only a few points are recorded at startup (in the CSV file) the rest are all zeros and reflect that in HyperTrend.

The Logger hardware is not robust. Does not feature a Time/Date stamp that Lookuot uses. This is going to hurt the business of that manufacturer, not using "the Universal time". The customer here is considering today to return the loggers and use NI loggers instead.

[Khalid]

Smigman,

There's one other (simpler?)way to do what you're doing. Instead of modifying the LKS file to make the following connections, you can make these connections dynamically within Lookout:

"Logger1.time1 = Table1.B1;
Logger1.time1 = Table1.B2;
Logger1.time1 = Table1.B3;
Logger1.time1 = Table1.B4;
...and so on."

Import your data into DataTable (using the Import datamember). And then use the Cursor to point to each row -- actually each cell. And traverse down the column using a timer. Cursor can be broadly considered as a cell-pointer. It points to a particular cell and will fetch that cell's value for you.

Very briefly, let's say you got all the times imported into column B. Create a Pulse, and then a Counter objec
t (counting these pulses). Use a Switch to reset the Counter (Counter.reset=!Switch). Connect this counter to the DataTable's cursor:

Table1.Cursor.1 = Counter1.

Then, in your Logger object, make the following connection:

Logger1.time1 = Table1.B.1

What we're saying here is "use the value from column B currently pointed to by the cursor 1."

Hit the Switch, see the Counter count up, and you'll see that it traverses down Column B giving you each cell's value. This value gets associated with your Logger's time member. Insert an Expression on Table1.B.1 and you'll see these update.

This way you don't have to edit the LKS file. BUT, if you're more comfy doing that, stick to it.

Good luck!

Khalid

[smigman]

I got an e-mail from NI.

Attached below is the "formula" for the time conversion in LOOKOUT.

I hope you, and others here, can use this too.

Use NOTEPAD or any text editor, to edit the LKS file.