Date Conversion

  • Thread starter Thread starter mecca
  • Start date Start date
M

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?
 
=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
 
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
 
mecca said:
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?
Click to expand...

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?
 
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)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Convert EML to PST in 2025? 2
Problem formatting dates from CSV file 2
Date format problems after csv import 2
Reformat a date imported from another program 3
Back to basics of excel 2003 4
convert date 1
Automatic filling of cells within a certain date range 1
Same Date a year ago 4

Back
Top