Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse cell value and convert to date
Hello newsgroup users and Gurus.
Your kind assistance please. This is a challenge. I have a column with a text string which is a date. i'e. 30121998= 30th December 1998. I would like to convert the text string into a actual date format. A tricky one I know, but is it possible. Regards Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse cell value and convert to date
Hi
one way: use the following formula in an adjacent cell: =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2)) and copy down -- Regards Frank Kabel Frankfurt, Germany PW wrote: Hello newsgroup users and Gurus. Your kind assistance please. This is a challenge. I have a column with a text string which is a date. i'e. 30121998= 30th December 1998. I would like to convert the text string into a actual date format. A tricky one I know, but is it possible. Regards Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse cell value and convert to date
Grüezi PW
PW schrieb am 10.06.2004 I have a column with a text string which is a date. i'e. 30121998= 30th December 1998. I would like to convert the text string into a actual date format. Try the following formula in an column: =DATEVALUE(TEXT(A1,"00-00-0000")) and format the cell(s) with 'dd.mmmm yyyy' -- Regards Thomas Ramel - MVP for Microsoft-Excel - [Win XP Pro SP-1 / xl2000 SP-3] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse cell value and convert to date
Hi Thomas
this kind of function could return wrong results if the regional date settings are for example MM-DD-YYYY and you have a date like 03042004 which should be 03-Apr-2004 but would be interpreted as 04-Mar-2004 Personally I'd trust DATEVALUE only for a format like YYYY-MM-DD -- Regards Frank Kabel Frankfurt, Germany Thomas Ramel wrote: Grüezi PW PW schrieb am 10.06.2004 I have a column with a text string which is a date. i'e. 30121998= 30th December 1998. I would like to convert the text string into a actual date format. Try the following formula in an column: =DATEVALUE(TEXT(A1,"00-00-0000")) and format the cell(s) with 'dd.mmmm yyyy' |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse cell value and convert to date
Grüezi Frank
Frank Kabel schrieb am 10.06.2004 this kind of function could return wrong results if the regional date settings are for example MM-DD-YYYY I was afraid so - but would the OP give it a try, because his notatin of the date looked like the 'European-way'. I'm aware of the date-problems and its international-issues. -- Regards Thomas Ramel - MVP for Microsoft-Excel - [Win XP Pro SP-1 / xl2000 SP-3] |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse cell value and convert to date
Thomas Ramel wrote:
Grüezi Frank Frank Kabel schrieb am 10.06.2004 this kind of function could return wrong results if the regional date settings are for example MM-DD-YYYY I was afraid so - but would the OP give it a try, because his notatin of the date looked like the 'European-way'. ack. In this case it probably should work I'm aware of the date-problems and its international-issues. Regards Frank |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reverse cell value and convert to date
Thomas, Frank, thank you both for your time.
You have have been helpful in solving my problem and also given me an insight into other issues that I may come across. Regards P -----Original Message----- Hello newsgroup users and Gurus. Your kind assistance please. This is a challenge. I have a column with a text string which is a date. i'e. 30121998= 30th December 1998. I would like to convert the text string into a actual date format. A tricky one I know, but is it possible. Regards Paul . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert worksheet string name to date format in cell | Excel Worksheet Functions | |||
how to paste the date or the row in the reverse order | Excel Discussion (Misc queries) | |||
How can I convert a text cell into date cell in Excel | Excel Discussion (Misc queries) | |||
How I convert date formatt of dd/mm/yy to mm/yy (not cell format) | Excel Discussion (Misc queries) | |||
Cell Date / Currency Convert | Excel Discussion (Misc queries) |