Excel – convert ten digit number into date excel

dateexcel

I have a CSV file which contains some dates. These dates are formatted as 10 digit numbers:
I have used the formula =TEXT(LEFT(A1,5),"dd-mmm-yyyy") but this returns the wrong date.

Then 10 digit numbers are with corresponding correct date:

1410666955 is 14-September 2014
1413514597 is 17-October 2014
1433822231 is 9-June 2015

Can anyone help me what formula to use to convert to the proper date?

Thanks to Cha, i've managed to convert the number into a Date.

The number was Unix based and i used the following formula:

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

The 9/24 is because i live in Melbourne Australia

Best Solution

Unix date format consists of the number of seconds that have passed since 1-Jan-1970.

Knowing that, it is just a matter of calculating how many days have passed, and then adding those days to 1-Jan-1970.

Here is the formula I used to convert those seconds into proper Dates:

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

Note that I use FLOOR to get rid of hours/minutes/seconds in that day. If you need more precission, don't use FLOOR

Here is the output I got: enter image description here

Related Question