How can I convert an exported UNIX timestamp in excel?

P

prod sorter

When I export a table using phpMyAdmin I get a date string of:

1064272434

Which equates to:

09/22/2003 07:13:54 PM

Is there a formula for excel that would make this conversion for me,
stripping the time of course?

Result:

09/22/2003
 
F

Fredrik Wahlgren

prod sorter said:
When I export a table using phpMyAdmin I get a date string of:

1064272434

Which equates to:

09/22/2003 07:13:54 PM

Is there a formula for excel that would make this conversion for me,
stripping the time of course?

Result:

09/22/2003
Click to expand...

Snipped from http://www.sins.com.au/unix/unix-timestamp.html

"This timestamp may be converted in EXCEL by using the following formula and
choosing the option for the "1904 base": =(x+((365*66+17)*86400))/86400 If
the "1900 base" is selected, then change the "66" value to "70". In Excel
Date 0.41667 = 10 hours. What I ended up using:"
=((B25+((365*70+19)*86400))/86400)-0.41667

/Fredrik
 
R

Ron Rosenfeld

When I export a table using phpMyAdmin I get a date string of:

1064272434

Which equates to:

09/22/2003 07:13:54 PM

Is there a formula for excel that would make this conversion for me,
stripping the time of course?

Result:

09/22/2003
Click to expand...

A Unix time stamp is the number of seconds since 1/1/1970. And it is usually
expressed as UTC.

I suspect that your "equates to" is local time. Are you 4 hrs behind Zulu
time?

Because when I translate that date/time stamp I get 9/22/03 23:13:54


In any event, to translate the UNIX timestamp into Windows PC time:

=unix_time/86400 - DATE(1970,1,1)

and to strip off the time:

=INT(=unix_time/86400 - DATE(1970,1,1))

However, you'd best get that time reference verified. The result does not make
sense unless there is some time zone manipulation going on.


--ron
 
Top