Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
|
|||
|
|||
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.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the serial date to a dd/mm/yyyy format | Excel Discussion (Misc queries) | |||
Dates converting to MS serial number | Excel Discussion (Misc queries) | |||
Converting Day, Date Month Year format to MM/DD/YYYY | Excel Worksheet Functions | |||
converting 8-digit number to date | Excel Discussion (Misc queries) | |||
Serial number of Date | Excel Worksheet Functions |