# How can I convert an exported UNIX timestamp in excel?

1. ## 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

2. ## Re: How can I convert an exported UNIX timestamp in excel?

"prod sorter" <prodsorter@discussions.microsoft.com> wrote in message
news:9DD4A2BA-7A0F-4B83-A1DA-4E98674CAE6D@microsoft.com...
> 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

3. ## Re: How can I convert an exported UNIX timestamp in excel?

"prod sorter" <prodsorter@discussions.microsoft.com> wrote in message
news:9DD4A2BA-7A0F-4B83-A1DA-4E98674CAE6D@microsoft.com...
> 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
>

http://exceltips.vitalnews.com/Pages...me_Stamps.html

/Fredrik

4. ## Re: How can I convert an exported UNIX timestamp in excel?

On Sun, 20 Mar 2005 10:27:02 -0800, "prod sorter"
<prodsorter@discussions.microsoft.com> 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

5. ## Re: How can I convert an exported UNIX timestamp in excel?

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

6. ## Re: How can I convert an exported UNIX timestamp in excel?

here's a UDF that will do it:

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

In article <9DD4A2BA-7A0F-4B83-A1DA-4E98674CAE6D@microsoft.com>,
"prod sorter" <prodsorter@discussions.microsoft.com> 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1