Timestamp in MS EXCEL (date into seconds)

[whizzz]

hello,

Iam trying to export data given in MS Excel to MS SQL Server 2000.
One field in Excel is nr_inst_dt (Installation Date) given in Date format.The corresponding attribute in SQL Server 2000 is of data type int(integer).Iam not allowed to alter the table structure since the front end uses this data in a timestamp manner.
So I want the date in excel to be converted into number of seconds from 1/1/1970 12:00 a.m that I can load into the corresponding integer attribute.

Is there any way in Excel that I can convert the date into number of seconds from 1/1/1979 12:00 a.m ?
Will there be any feature like 'timestamp' which I can make use of ?

Thanks in advance

 

[TonyJollans]

Hi whizzz,

No integer timestamp. Dates and Times are held as numbers of days (including fractional parts of days) since 30/12/1899.

To get what you want you need a two stage process.

* Find the difference (in days) between the date you have and your base date.
* Multiply by the number of seconds in a day (86400).

If your date is in A1, then =(A1-"1/1/1970&quot*86400 should give you your answer (assuming by 12:00 am you mean midnight).

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.