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

  • Thread starter Thread starter Reenee
  • Start date Start date
R

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.
 
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.
 
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.
 
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)
 
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
 
Kassie said:
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.
>
Click to expand...
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..
 
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
 
Back
Top