|
Converting a 10 digit serial number to a date of mm/dd/yyyy
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. |
Answer: Converting a 10 digit serial number to a date of mm/dd/yyyy
To apply this formula to multiple cells, you can use the Fill Handle. Here's how:
The formula should now be applied to all the selected cells, and the dates should be displayed in the format mm/dd/yyyy. |
Converting a 10 digit serial number to a date of mm/dd/yyyy
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. -- Regards, OssieMac "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. |
Converting a 10 digit serial number to a date of mm/dd/yyyy
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. |
Converting a 10 digit serial number to a date of mm/dd/yyyy
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) -- Biff Microsoft Excel MVP "Reenee" wrote in message ... 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. |
Converting a 10 digit serial number to a date of mm/dd/yyyy
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 -- If this helps, please click the Yes button Cheers, Shane Devenshire "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. |
Converting a 10 digit serial number to a date of mm/dd/yyyy
Rather than 864, do you mean 86400, Shane ?
86400 is 24 * 60 * 60, so is the number of seconds in a day. -- David Biddulph "Shane Devenshire" wrote in message ... 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 -- If this helps, please click the Yes button Cheers, Shane Devenshire "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. |
Converting a 10 digit serial number to a date of mm/dd/yyyy
You are a genious this did it and thank you so much, you saved me!!!
"Kassie" wrote: 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. |
| All times are GMT +1. The time now is 08:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com