Looking For A Smart Member Here.

[Spindulik]

I need to know how to convert a date into a numeric integer. I am looking for a precise formula. This includes a leap year, that must be exact to the millisecond.

For example:
January 1, 1900 is 2.0
January 2, 1900 is 3.0, and so on.
June 6, 1994 is 34491.5

[Cheese]

Half an hour and no response to anything in the lounge...Spindulik I think your maths problem might have killed some members...

[Emdee]

U should maybe try some kind of programming forum or something. There's bound to be some nerd out there who'll come up with the answer right away

[JONNO_CELEBS]

ARMADILO'S!!!!!

Jonno B)

[FlyingDutchman]

Originally posted by Spindulik@6 February 2004 - 01:07
I need to know how to convert a date into a numeric integer. I am looking for a precise formula. This includes a leap year, that must be exact to the millisecond.

For example:
January 1, 1900 is 2.0
January 2, 1900 is 3.0, and so on.
June 6, 1994 is 34491.5

What your looking for isn't a formula.

What you need is a routine, FUNCTION or SUB, as it is called among programmers.

Programming is done in a language.
Are you thinking in language, and if so, what language?
Or is it your need some way to do calculations in Excel?

Could you give an example of any formula that creates a string as output?

[Spindulik]

Originally posted by FlyingDutchman+5 February 2004 - 22:09--></div><table border='0' align='center' width='95%' cellpadding='3' cellspacing='1'><tr><td>QUOTE (FlyingDutchman &#064; 5 February 2004 - 22:09)</td></tr><tr><td id='QUOTE'><!--QuoteBegin-Spindulik@6 February 2004 - 01:07
I need to know how to convert a date into a numeric integer. I am looking for a precise formula. This includes a leap year, that must be exact to the millisecond.

For example:
January 1, 1900 is 2.0
January 2, 1900 is 3.0, and so on.
June 6, 1994 is 34491.5

What your looking for isn&#39;t a formula.

What you need is a routine, FUNCTION or SUB, as it is called among programmers.

Programming is done in a language.
Are you thinking in language, and if so, what language?
Or is it your need some way to do calculations in Excel?

Could you give an example of any formula that creates a string as output?[/b][/quote]
I am fluent with Visual Basic, C/C++, Lookout 5.1 and Ladder Logic IEC 1131.

The formula that I need is something like this...

result = (year - 1900) + ((year - 1901) / 4))

...from memory, this is what I got but it is off by a few days (it is not correct).

[I.am]

What do you need this for? Are you going to use it somewhere in a program? There might be alternate ways to do it.


This is in java,

 
private static int daysSince1900( int day, int month, int year ) {
        
        int fullYears = year - 1900;
        int leapYears = ( year - 1901 ) / 4;  // To calculate *full* leap years, centuries,
        int centuries = ( year - 1901 ) / 100;  // etc. since 1900, I count from 1901 (or 1601,
        int fourCenturies = ( year - 1601 ) / 400;	// for quadruple centuries) to avoid counting the current year, which isn't full yet.
        return   fullYears * 365 + leapYears - centuries + fourCenturies
               + daysIntoYear( month, year )
               + day - 1;
    }

[FlyingDutchman]

FUNCTION DATNUM (dat&#036
&nbsp; ON ERROR GOTO NonGlobalHandler
&nbsp; dd = VAL(dat&#036
&nbsp; mm = VAL(MID&#036;(dat&#036;, 4))
&nbsp; j& = VAL(MID&#036;(dat&#036;, 7))
&nbsp; IF dd = 0 OR mm = 0 THEN DATNUM = 32767: EXIT FUNCTION
&nbsp; IF j& < 100 THEN j& = j& + 1900
&nbsp; SELECT CASE mm
&nbsp; &nbsp; CASE 3: dd = dd + 1
&nbsp; &nbsp; CASE 4: dd = dd + 32
&nbsp; &nbsp; CASE 5: dd = dd + 62
&nbsp; &nbsp; CASE 6: dd = dd + 93
&nbsp; &nbsp; CASE 7: dd = dd + 123
&nbsp; &nbsp; CASE 8: dd = dd + 154
&nbsp; &nbsp; CASE 9: dd = dd + 185
&nbsp; &nbsp; CASE 10: dd = dd + 215
&nbsp; &nbsp; CASE 11: dd = dd + 246
&nbsp; &nbsp; CASE 12: dd = dd + 276
&nbsp; &nbsp; CASE 1: dd = dd + 307: j& = j& - 1
&nbsp; &nbsp; CASE ELSE: IF dd > 28 THEN dd = dd - 28 ELSE dd = dd + 338: j& = j& - 1
&nbsp; END SELECT
&nbsp; DATNUM = -32767 + ((j& - 1876) &#092; 4) * 1461 + (j& MOD 4) * 365 + dd
END FUNCTION

FUNCTION DATUM&#036; (d)
&nbsp; ON ERROR GOTO NonGlobalHandler
&nbsp; d& = d + 32767&
&nbsp; jj = (d& &#092; 1461) * 4 + 1876&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#39;1876 is een schrikkeljaar
&nbsp; rest = d& MOD 1461
&nbsp; DO WHILE rest > 366
&nbsp; &nbsp; rest = rest - 365
&nbsp; &nbsp; jj = jj + 1
&nbsp; LOOP
&nbsp; &nbsp; SELECT CASE rest
&nbsp; &nbsp; CASE IS < 2: md&#036; = "-02-": rest = rest + 28
&nbsp; &nbsp; CASE 2 TO 32: md&#036; = "-03-": rest = rest - 1
&nbsp; &nbsp; CASE IS < 63: md&#036; = "-04-": rest = rest - 32
&nbsp; &nbsp; CASE IS < 94: md&#036; = "-05-": rest = rest - 62
&nbsp; &nbsp; CASE IS < 124: md&#036; = "-06-": rest = rest - 93
&nbsp; &nbsp; CASE IS < 155: md&#036; = "-07-": rest = rest - 123
&nbsp; &nbsp; CASE IS < 186: md&#036; = "-08-": rest = rest - 154
&nbsp; &nbsp; CASE IS < 216: md&#036; = "-09-": rest = rest - 185
&nbsp; &nbsp; CASE IS < 247: md&#036; = "-10-": rest = rest - 215
&nbsp; &nbsp; CASE IS < 277: md&#036; = "-11-": rest = rest - 246
&nbsp; &nbsp; CASE IS < 308: md&#036; = "-12-": rest = rest - 276
&nbsp; &nbsp; CASE IS < 339: md&#036; = "-01-": rest = rest - 307: jj = jj + 1
&nbsp; &nbsp; CASE ELSE:&nbsp; &nbsp; md&#036; = "-02-": rest = rest - 338: jj = jj + 1
&nbsp; &nbsp; END SELECT
&nbsp; IF jj = 2055 THEN
&nbsp; &nbsp; DATUM&#036; = "&nbsp; -&nbsp; -&nbsp; &nbsp; "
&nbsp; ELSE
&nbsp; &nbsp; DATUM&#036; = RIGHT&#036;("0" + MID&#036;(STR&#036;(rest), 2), 2) + md&#036; + MID&#036;(STR&#036;(jj), 2)
&nbsp; END IF
END FUNCTION

These two should get you started.
Dates from 1880 to 2055 can be stored in 2 bytes or 16 bit integers

It should be easy to add milliseconds.
The hard part is done

[Spindulik]

Originally posted by I.am@5 February 2004 - 22:29
What do you need this for? Are you going to use it somewhere in a program? There might be alternate ways to do it.

Here&#39;s the deal.
There are nine different DATA Loggers at remote places (accessed by modem). Each DATA logger uses Modbus protocol, which means they store numbers that represent stored DATA (in registers). Some of the numbers represent a 4-20MA signal, scaled to Feet and Inches (later on). Other numbers are Months, Days, year, hours and minutes.

I need to convert the date (and time) into a integer so that the software at the central computer can associate the feet and inches at the correct time, and order.

"Basic" language is preferred, but I may be able to convert the JAVA example. I am using the software package called "Lookout 5.1" for this project.


BTW, the dates for GMT always reffer to Jan 1, 1900 and later.

[Spindulik]

I got the answer from an expert&#33;


Exp1 = new "06/06/2004 11:20:52.22 AM";

Exp2 = new left(Exp1,10);

Exp3 = new mid(Exp1,12,2)/24+mid(Exp1,15,2)/(24*60)+mid(Exp1,18,5)/(24*60*60);

Exp4 = new left(Exp1,10)+mid(Exp1,12,2)/24+mid(Exp1,15,2)/(24*60)+mid(Exp1,18,5)/(24*60*60);


We want to convert 06/06/2004 11:20:52.22 AM into a number.

EXP 4 is the ROUNDED answer to the calculations (Not 100% accurate).

But if you add the results of EXP2 and EXP3 you get the exact time and date down to the exact millisecond&#33;


EXP2 + EXP3 = Most Accurate Answer

OR

38144 + 0.472827 = 38144.472827


If you have EXCEL, copy and paste that number into it and convert it into a date. You&#39;ll see that EXCEL sees the date prefectly. Actually, EXcel is only accurate down to the second, not the millisecond.