Converting a 10 digit serial number to a date of mm/dd/yyyy

[Reenee]

I have exported a report from my payroll software that gave me a 12 digit
serial number (not text) and that number is supposed to represent hire date,
for example: 3344630400

How do I change this 10 digit number to read mm/dd/yyyy.

I have tried all I know please help

Someone told me that I had to divide this number by 864,000 + 2, but I have
4000+ cells that need to be updated and I know there has to be a faster way.

 

[OssieMac]

It will help if you know what date is represented by 3344630400. I am
thinking that it might be a serial date AND TIME but with the decimal point
omitted but it is difficult to say what it is without knowing what it
represents.

 

[Kassie]

33446304 devided by 864,000 (aqs in thousands) +2 does not result in a number
that would represent a modern day date. If you change that to 864, and
format the result as date, you will get 27/12/2005. Now if this date is
correct, you are halfway there. Insert a formula in a vacant cell Say C1,
next to the cell containing the number, say B1 and insert the formula
=(B1/864)+2. Copy down as far as required. Format this column as date, copy
the column, paste it special in place as values, and you have your dates.

 

[T. Valko]

3344630400

If that's a Unix date stamp:

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

Returns as a formatted date: 12/27/2075 (m/d/y)

If it's an Excel based date stamp:

=A1/86400

Returns as a formatted date: 12/25/2005 (m/d/y)

 

[Shane Devenshire]

Hi,

1. Enter 864 in a cell
2. Copy the cell
3. Select all the cells with the serial number
4. Choose Edit, Paste Special, Divide

 

[Reenee]

You are a genious this did it and thank you so much, you saved me!!!

 

[assomasu]

33446304 devided by 864,000 (aqs in thousands) +2 does not result in a number
that would represent a modern day date. If you change that to 864, and
format the result as date, you will get 27/12/2005. Now if this date is
correct, you are halfway there. Insert a formula in a vacant cell Say C1,
next to the cell containing the number, say B1 and insert the formula
=(B1/864)+2. Copy down as far as required. Format this column as date, copy
the column, paste it special in place as values, and you have your dates.

--
Hth

Kassie Kasselman
Change xxx to hotmail


"Reenee" wrote:

> I have exported a report from my payroll software that gave me a 12 digit
> serial number (not text) and that number is supposed to represent hire date,
> for example: 3344630400
>
> How do I change this 10 digit number to read mm/dd/yyyy.
>
> I have tried all I know please help
>
> Someone told me that I had to divide this number by 864,000 + 2, but I have
> 4000+ cells that need to be updated and I know there has to be a faster way.
>

I have same problem, after apply your solution i can not get the expected results.

PracticalUpdatedOn: 1233118190
TestUpdatedOn: 0

Expected Date: 28-Jan-2009 (1233118190)
Can you please help me out..

 

[saraudpm]

Hi I tried this way, but it does not give exact data and time. Please help me out and let me explain below,
1295730471 is the date & time format in database, if I change it as per above quote 1295730471/86400+2=1/22/1941 9:07:51 PM

But the value suppose to be 1/22/2011 9:07:51 PM, only I am facing problem with year