Date Conversion

[mecca]

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?

 

[David Biddulph]

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

 

[T. Valko]

=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

 

[Bernard Liengme]

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

 

[Glenn]

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.

 

[Glenn]

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

 

[Rick Rothstein]

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)

 

[David Biddulph]

Presumably standard Unix time?
--
David Biddulph

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