Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion
=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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion
=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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Conversion | Excel Discussion (Misc queries) | |||
date conversion | Excel Discussion (Misc queries) | |||
Date Conversion | Excel Discussion (Misc queries) | |||
Date Conversion | Excel Discussion (Misc queries) | |||
Date Conversion - sorry | New Users to Excel |