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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]> 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 <[email protected]>,
"prod sorter" <[email protected]> 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