How can I convert an exported UNIX timestamp in excel?
 

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

"prod sorter" wrote in message
...
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


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

"prod sorter" wrote in message
...
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


Yeat another link:
http://exceltips.vitalnews.com/Pages...me_Stamps.html

/Fredrik

On Sun, 20 Mar 2005 10:27:02 -0800, "prod sorter"
wrote:

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

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

And J.E. McGimpsey has both a worksheet formula and a UDF at:
http://www.mcgimpsey.com/excel/udfs/unixtoxltime.html

prod sorter wrote:

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

--

Dave Peterson

here's a UDF that will do it:

http://mcgimpsey.com/excel/udfs/unixtoxltime.html

In article ,
"prod sorter" wrote:

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