| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date function
If I have dates listed in a column as 06-0101 etc, how can I format it to
be: 20060101? I have 7000 rows where I need to reformat the date. Help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date function
Pam,
Assuming your dates are in column A, put the following formula in column B =DATE("20"&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pam Coleman" wrote in message ... If I have dates listed in a column as 06-0101 etc, how can I format it to be: 20060101? I have 7000 rows where I need to reformat the date. Help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date function
Are those yy-mmdd
If yes and yy are all in this century, you could use a helper cell and this formula: =--TEXT(--SUBSTITUTE(A1,"-",""),"\2\000\/00\/00") format as date and drag down. Pam Coleman wrote: If I have dates listed in a column as 06-0101 etc, how can I format it to be: 20060101? I have 7000 rows where I need to reformat the date. Help. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date function
You didn't say if the data in your cells is entered as a date and that the format you currently have displays them as 06-0101 or if this is just text that you have entered. If they are truly entered as dates, you can create a custom format: 1. Click on FormatCellsCustom. 2. Type in: yyyymmdd 3. Click on OK. -- neopolitan ------------------------------------------------------------------------ neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611 View this thread: http://www.excelforum.com/showthread...hreadid=504117 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date function
What if the first 2 numbers is the year and the other are our complaint number:
02-12345 how could I make it read 200212345? Some of our information this year is going to contain a complaint number instead of the full date. Thanks, "Chip Pearson" wrote: Pam, Assuming your dates are in column A, put the following formula in column B =DATE("20"&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pam Coleman" wrote in message ... If I have dates listed in a column as 06-0101 etc, how can I format it to be: 20060101? I have 7000 rows where I need to reformat the date. Help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date function
Maybe just:
"20"&substitute(a1,"-","") Pam Coleman wrote: What if the first 2 numbers is the year and the other are our complaint number: 02-12345 how could I make it read 200212345? Some of our information this year is going to contain a complaint number instead of the full date. Thanks, "Chip Pearson" wrote: Pam, Assuming your dates are in column A, put the following formula in column B =DATE("20"&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pam Coleman" wrote in message ... If I have dates listed in a column as 06-0101 etc, how can I format it to be: 20060101? I have 7000 rows where I need to reformat the date. Help. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date function in Excel that updates only when a doc is changed? | Excel Worksheet Functions | |||
| Date Function | Excel Discussion (Misc queries) | |||
| date format and the RIGHT function | Excel Worksheet Functions | |||
| Date function | Excel Worksheet Functions | |||
| Is there a function to show future date | Excel Worksheet Functions | |||
Linear Mode
