| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date format
Hi,
I need some advice on a formula to reformat a Numbers in column A into a Date format in column B i.e. A B 19600321 changed to 21/03/1960 19850723 changed to 23/07/1985 19770707 changed to 07/07/1977 Regards, Ward |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date format
Cell A1 = RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)
If this post helps click Yes --------------- Jacob Skaria "Ward" wrote: Hi, I need some advice on a formula to reformat a Numbers in column A into a Date format in column B i.e. A B 19600321 changed to 21/03/1960 19850723 changed to 23/07/1985 19770707 changed to 07/07/1977 Regards, Ward |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date format
thank you
"Jacob Skaria" wrote: Cell A1 = RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) If this post helps click Yes --------------- Jacob Skaria "Ward" wrote: Hi, I need some advice on a formula to reformat a Numbers in column A into a Date format in column B i.e. A B 19600321 changed to 21/03/1960 19850723 changed to 23/07/1985 19770707 changed to 07/07/1977 Regards, Ward |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date format
Or, if you want an Excel date
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) and format it as you like. Regards, Stefi €˛Ward€¯ ezt Ć*rta: thank you "Jacob Skaria" wrote: Cell A1 = RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) If this post helps click Yes --------------- Jacob Skaria "Ward" wrote: Hi, I need some advice on a formula to reformat a Numbers in column A into a Date format in column B i.e. A B 19600321 changed to 21/03/1960 19850723 changed to 23/07/1985 19770707 changed to 07/07/1977 Regards, Ward |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date format
Hi,
You can also try this (no-formula) approach. Highlight A2:A4 and go to Data Text to columns. Now click on Next two times. On the third screen select the column data format as date and select YMD in the drop down. Select the destination cell as B2 and click on Finish -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Ward" wrote in message ... Hi, I need some advice on a formula to reformat a Numbers in column A into a Date format in column B i.e. A B 19600321 changed to 21/03/1960 19850723 changed to 23/07/1985 19770707 changed to 07/07/1977 Regards, Ward |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date format
Hi,
This way is working better for me because I don't have to multiply the new text field by 1 and then chose date cell format it does it automatically "Ashish Mathur" wrote: Hi, You can also try this (no-formula) approach. Highlight A2:A4 and go to Data Text to columns. Now click on Next two times. On the third screen select the column data format as date and select YMD in the drop down. Select the destination cell as B2 and click on Finish -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Ward" wrote in message ... Hi, I need some advice on a formula to reformat a Numbers in column A into a Date format in column B i.e. A B 19600321 changed to 21/03/1960 19850723 changed to 23/07/1985 19770707 changed to 07/07/1977 Regards, Ward |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date format
=--TEXT(A2,"0000\-00\-00") and format appropriately
-- David Biddulph "Ward" wrote in message ... Hi, I need some advice on a formula to reformat a Numbers in column A into a Date format in column B i.e. A B 19600321 changed to 21/03/1960 19850723 changed to 23/07/1985 19770707 changed to 07/07/1977 Regards, Ward |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date format
You could also use DataText to ColumnsNextNextColumn Data
FormatDateYMD and finish. No helper column needed. Gord Dibben MS Excel MVP On Fri, 27 Mar 2009 16:30:00 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: =--TEXT(A2,"0000\-00\-00") and format appropriately -- David Biddulph "Ward" wrote in message ... Hi, I need some advice on a formula to reformat a Numbers in column A into a Date format in column B i.e. A B 19600321 changed to 21/03/1960 19850723 changed to 23/07/1985 19770707 changed to 07/07/1977 Regards, Ward |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
| Convert date from text format to date format | Excel Discussion (Misc queries) | |||
| Convert date + time text format to date format | Excel Worksheet Functions | |||
| Can I change a date with no format (20051111) to date format? | New Users to Excel | |||
| Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
Linear Mode
