Can't detemine date(s)

[mpurtell]

I received a set of records in spreadsheet format from our IT folks that
includes fields that are supposed to be dates. Here's a sample of those date
field values;

1024963200, 1033430400, 1026086400, 1036108800, 1024963200, 1035331200.

But I can't figure out any way to get a date out of them. They all should be
later than 1/1/1995.

I believe the records are from an Oracle DB.

Can anybody provide any pointers here???

Thanks in advance,
Mike Purtell

 

[Ron Rosenfeld]

I received a set of records in spreadsheet format from our IT folks that
includes fields that are supposed to be dates. Here's a sample of those date
field values;

1024963200, 1033430400, 1026086400, 1036108800, 1024963200, 1035331200.

But I can't figure out any way to get a date out of them. They all should be
later than 1/1/1995.

I believe the records are from an Oracle DB.

Can anybody provide any pointers here???

Thanks in advance,
Mike Purtell

See if this results in sensible dates:
(If Oracle is using the UNIX system, where a date is represented by the
number of seconds since 1/1/1970, this may work)

=DateField/86400 + 25569

Format as date.




--ron

 

[mpurtell]

Ron,

Well at leas that provides a date!

But the year should be >= 199n and I get circa 1973 for these values.

Still searching...

-Mike

 

[Myrna Larson]

Did you ask your IT folks what the procedure is to translate thes numbers into a readable date?
They are probably offsets from some base date; the question is, what is that "Day 0"? Another
question is whether the data represents just a date or a date and time.

 

[Dave Peterson]

I copied your data into a workbook (with 1900 as the base date) and used Ron's
formula.

1024963200 06/25/2002
1033430400 10/01/2002
1026086400 07/08/2002
1036108800 11/01/2002
1024963200 06/25/2002
1035331200 10/23/2002

Make sure you replace Datefield with the address containing the numbers, like:
=A1/86400 + 25569

 

[mpurtell]

Thanks Ron, Myrna & Dave.
You are correct, Ron (and everybody else).

For some fickle reason the field formula works just as advertised!
I've had the flu for the last 2 weeks and it's, to say the least slowed me
down. Perhaps my spreadsheet had a touch of it. coff coff ;-\


Now...
Can anybody explain the "magic numbers"
864000
and
25569
?

Mike

 

[EZ Money]

86400 is the number of seconds in a day (24*60*60). I have no clue as to the
significance of 25569.

 

[Harlan Grove]

25569 is the equivalent of the date 1-Jan-1970, which is date on
which Unix systems base their date offsets. Therefore, a Unix date
of 1 refers to VB date of 25569.

Note that such Unix 'dates' are unsigned long integers and are the number of
*SECONDS* since midnight UTC on 1-Jan-1970. So Unix 'dates' (systimes would be
the more appropriate term) of 0-86399 correspond to the VB date 25569.