UTC time to standard time

[dennis]

I have an sql database that I am importing data from, into excel. One of
the fields has a 32 bit integer that represents the number of seconds
elapsed from 1/1/70 to current time based on UTC. I would like to convert
this to a standard excel date and time. Anyone know an easy way to do
this. I know I could write a function that would use divison and modela
functions to arrive at the answer, but I thought there might be an easier
way.

Any suggestions appreciated.

Dennis

 

[Peo Sjoblom]

You could use this formula

=integer/86400+"01/01/1970"

can be written as

=integer/86400+25569

so you could put 86400 in one cell and 25569 in another, then copy the
86400,
select the imported integers and do edit>paste special and check divide,
then copy
the 25569, select the data and paste specials and select add, then use a
format like


mm/dd/yy hh:mm:ss


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[dennis]

That gives me the "date" but what about the actual time?

thanks for your help.

Dennis

 

[Dave Peterson]

Try formatting the cell with the formula like:

mm/dd/yyyy hh:mm:ss

(or just include the time)