I import a date from another program which converts the date from 12/19/2008
to 1,229,666,400. How do I get excel to convert it back?

=A1/(24*60*60)+DATE(1970,1,1)
--
David Biddulph

mecca wrote:
I import a date from another program which converts the date from
12/19/2008 to 1,229,666,400. How do I get excel to convert it back?

=DATE(1970,1,1)+A1/86400

This includes a *time*.

39801.25 when formatted is 12/19/2008 6:00 AM

If you want only the date portion:

=INT(DATE(1970,1,1)+A1/86400)

Format as date = 12/19/2008

--
Biff
Microsoft Excel MVP


"mecca" wrote in message
...
I import a date from another program which converts the date from
12/19/2008
to 1,229,666,400. How do I get excel to convert it back?

We need more examples to be able to work out how 12/19/2008 became
1,229,666,400.
Or do you know the algorithm?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"mecca" wrote in message
...
I import a date from another program which converts the date from

mecca wrote:
I import a date from another program which converts the date from 12/19/2008
to 1,229,666,400. How do I get excel to convert it back?

With 1,229,666,400 in A1, put this in B1 and format it as date:

=A1-1229626599

But that might only work for this particular example. If so, maybe if you
provided a few other data points someone could figure it out.

So, 1,229,666,400 is the number of seconds since January 1st, 1970?

David Biddulph wrote:
=A1/(24*60*60)+DATE(1970,1,1)
--
David Biddulph

mecca wrote:
I import a date from another program which converts the date from
12/19/2008 to 1,229,666,400. How do I get excel to convert it back?

You can save a function call by using a date text string...

Date plus time: ="1/1/1970"+A1/86400

Date only: =INT("1/1/1970"+A1/86400)

--
Rick (MVP - Excel)


"T. Valko" wrote in message ...
=DATE(1970,1,1)+A1/86400

This includes a *time*.

39801.25 when formatted is 12/19/2008 6:00 AM

If you want only the date portion:

=INT(DATE(1970,1,1)+A1/86400)

Format as date = 12/19/2008

--
Biff
Microsoft Excel MVP


"mecca" wrote in message
...
I import a date from another program which converts the date from
12/19/2008
to 1,229,666,400. How do I get excel to convert it back?

Presumably standard Unix time?
--
David Biddulph

Glenn wrote:
So, 1,229,666,400 is the number of seconds since January 1st, 1970?

David Biddulph wrote:
=A1/(24*60*60)+DATE(1970,1,1)
--
David Biddulph

mecca wrote:
I import a date from another program which converts the date from
12/19/2008 to 1,229,666,400. How do I get excel to convert it back?