View Full Version : Looking For A Smart Member Here.
Spindulik
02-06-2004, 01:07 AM
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
02-06-2004, 01:33 AM
Half an hour and no response to anything in the lounge...Spindulik I think your maths problem might have killed some members...
Emdee
02-06-2004, 01:59 AM
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 :P
JONNO_CELEBS
02-06-2004, 02:01 AM
:01: ARMADILO'S!!!!! :01:
Jonno B)
FlyingDutchman
02-06-2004, 02:09 AM
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
02-06-2004, 02:22 AM
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 @ 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'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).
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
02-06-2004, 02:41 AM
FUNCTION DATNUM (dat$)
ON ERROR GOTO NonGlobalHandler
dd = VAL(dat$)
mm = VAL(MID$(dat$, 4))
j& = VAL(MID$(dat$, 7))
IF dd = 0 OR mm = 0 THEN DATNUM = 32767: EXIT FUNCTION
IF j& < 100 THEN j& = j& + 1900
SELECT CASE mm
CASE 3: dd = dd + 1
CASE 4: dd = dd + 32
CASE 5: dd = dd + 62
CASE 6: dd = dd + 93
CASE 7: dd = dd + 123
CASE 8: dd = dd + 154
CASE 9: dd = dd + 185
CASE 10: dd = dd + 215
CASE 11: dd = dd + 246
CASE 12: dd = dd + 276
CASE 1: dd = dd + 307: j& = j& - 1
CASE ELSE: IF dd > 28 THEN dd = dd - 28 ELSE dd = dd + 338: j& = j& - 1
END SELECT
DATNUM = -32767 + ((j& - 1876) \ 4) * 1461 + (j& MOD 4) * 365 + dd
END FUNCTION
FUNCTION DATUM$ (d)
ON ERROR GOTO NonGlobalHandler
d& = d + 32767&
jj = (d& \ 1461) * 4 + 1876 '1876 is een schrikkeljaar
rest = d& MOD 1461
DO WHILE rest > 366
rest = rest - 365
jj = jj + 1
LOOP
SELECT CASE rest
CASE IS < 2: md$ = "-02-": rest = rest + 28
CASE 2 TO 32: md$ = "-03-": rest = rest - 1
CASE IS < 63: md$ = "-04-": rest = rest - 32
CASE IS < 94: md$ = "-05-": rest = rest - 62
CASE IS < 124: md$ = "-06-": rest = rest - 93
CASE IS < 155: md$ = "-07-": rest = rest - 123
CASE IS < 186: md$ = "-08-": rest = rest - 154
CASE IS < 216: md$ = "-09-": rest = rest - 185
CASE IS < 247: md$ = "-10-": rest = rest - 215
CASE IS < 277: md$ = "-11-": rest = rest - 246
CASE IS < 308: md$ = "-12-": rest = rest - 276
CASE IS < 339: md$ = "-01-": rest = rest - 307: jj = jj + 1
CASE ELSE: md$ = "-02-": rest = rest - 338: jj = jj + 1
END SELECT
IF jj = 2055 THEN
DATUM$ = " - - "
ELSE
DATUM$ = RIGHT$("0" + MID$(STR$(rest), 2), 2) + md$ + MID$(STR$(jj), 2)
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 :rolleyes:
Spindulik
02-06-2004, 02:47 AM
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'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
02-09-2004, 11:06 PM
I got the answer from an expert!
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!
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'll see that EXCEL sees the date prefectly. Actually, EXcel is only accurate down to the second, not the millisecond.
Powered by vBulletin® Version 4.2.3 Copyright © 2024 vBulletin Solutions, Inc. All rights reserved.