| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse and format string
I receive date data in yyyymmdd format eg, 20061031 = 31 Oct 2006.
I want to transpose the data to dd-mm-yyyy format. Prefer not to use VBA. I thought I had seen an in cell format using the text() & mid() expressions but cannot reproduce it. Tks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reverse and format string
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Format as Date -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike" wrote in message ... |I receive date data in yyyymmdd format eg, 20061031 = 31 Oct 2006. | I want to transpose the data to dd-mm-yyyy format. | Prefer not to use VBA. | I thought I had seen an in cell format using the text() & mid() expressions | but cannot reproduce it. | | Tks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reverse and format string
another way: in B1 enter
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) then format B1 - Custom dd-mm-yyyy "Mike" wrote: I receive date data in yyyymmdd format eg, 20061031 = 31 Oct 2006. I want to transpose the data to dd-mm-yyyy format. Prefer not to use VBA. I thought I had seen an in cell format using the text() & mid() expressions but cannot reproduce it. Tks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Reverse and format string
Without formulas and helper cells.
DataText to ColumnsNextNextColumn Data FormatDateYMDFinish. Format to your liking. Gord Dibben MS Excel MVP On Tue, 31 Oct 2006 03:56:01 -0800, Mike wrote: I receive date data in yyyymmdd format eg, 20061031 = 31 Oct 2006. I want to transpose the data to dd-mm-yyyy format. Prefer not to use VBA. I thought I had seen an in cell format using the text() & mid() expressions but cannot reproduce it. Tks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| new workbook format is in reverse | Excel Discussion (Misc queries) | |||
| Reverse column order with a macro | Excel Discussion (Misc queries) | |||
| CSV Date Format and General Arrrggghhh-ness | Excel Discussion (Misc queries) | |||
| Printing same table on reverse side of all pages. | Excel Discussion (Misc queries) | |||
| How do I reverse a spreadsheet that is in the format of a subtota. | Excel Discussion (Misc queries) | |||
Linear Mode
