Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting general text format to date
I've received a report whereby column A shows a list of dates.
Unfortunately, it seems that the date is written in a 'general' format and looks like this: Sunday, June 15, 2008 Is there an easy way to convert this into a date format, i.e. dd/mm/yy? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting general text format to date
With your value in A1 use:
=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1))) and format it as you choose -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: I've received a report whereby column A shows a list of dates. Unfortunately, it seems that the date is written in a 'general' format and looks like this: Sunday, June 15, 2008 Is there an easy way to convert this into a date format, i.e. dd/mm/yy? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting general text format to date
Assuming your date data in Column A starts at Row 2, put this formula in Row
2 of an unused (helper) column.... =--MID(A1,FIND(",",A1)+2,20) and copy it down. Next, select this entire column and press Ctrl+C (or click Edit/Copy on the menu bar), click on A2 and click Edit/PasteSpecial on the menu bar, select the Values option under in the Paste section and click OK, press escape to clear the operation and, finally, delete the helper column. If your dates look like 5-digit numbers, select the column and use Format Cells to format the displayed dates as you want. Rick "Sarah (OGI)" wrote in message ... I've received a report whereby column A shows a list of dates. Unfortunately, it seems that the date is written in a 'general' format and looks like this: Sunday, June 15, 2008 Is there an easy way to convert this into a date format, i.e. dd/mm/yy? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting general text format to date
Thanks for your prompt response, but I'm getting a #VALUE! result. Any ideas
why this might be? "Gary''s Student" wrote: With your value in A1 use: =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1))) and format it as you choose -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: I've received a report whereby column A shows a list of dates. Unfortunately, it seems that the date is written in a 'general' format and looks like this: Sunday, June 15, 2008 Is there an easy way to convert this into a date format, i.e. dd/mm/yy? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting general text format to date
The formula will work if the text starts with a day followed by a comma
followed by a single space. Which is what your example shows. -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: Thanks for your prompt response, but I'm getting a #VALUE! result. Any ideas why this might be? "Gary''s Student" wrote: With your value in A1 use: =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1))) and format it as you choose -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: I've received a report whereby column A shows a list of dates. Unfortunately, it seems that the date is written in a 'general' format and looks like this: Sunday, June 15, 2008 Is there an easy way to convert this into a date format, i.e. dd/mm/yy? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting general text format to date
Yes, the text does start with a day followed by a comma followed by a single
space. I've tried the formula you suggested again but have excluded the DATEVALUE section (so I've entered 'RIGHT(A1,LEN(A1)-FIND(" ",A1,1))'), which gives the result of: June 15, 2008 As soon as I include the DATEVALUE, I get the #VALUE! result. I've tried it on a new spreadsheet and a new session of Excel, just in case there was a problem with the source data. "Gary''s Student" wrote: The formula will work if the text starts with a day followed by a comma followed by a single space. Which is what your example shows. -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: Thanks for your prompt response, but I'm getting a #VALUE! result. Any ideas why this might be? "Gary''s Student" wrote: With your value in A1 use: =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1))) and format it as you choose -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: I've received a report whereby column A shows a list of dates. Unfortunately, it seems that the date is written in a 'general' format and looks like this: Sunday, June 15, 2008 Is there an easy way to convert this into a date format, i.e. dd/mm/yy? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting general text format to date
This is very interesting.
If you start with a clean worksheet, format cell A1 as Text and enter: June 15, 2008 with only single spaces internally and no leading or trailing spaces. In another cell, enter: =DATEVALUE(A1) you should see: 39614 which is the date in number format. -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: Yes, the text does start with a day followed by a comma followed by a single space. I've tried the formula you suggested again but have excluded the DATEVALUE section (so I've entered 'RIGHT(A1,LEN(A1)-FIND(" ",A1,1))'), which gives the result of: June 15, 2008 As soon as I include the DATEVALUE, I get the #VALUE! result. I've tried it on a new spreadsheet and a new session of Excel, just in case there was a problem with the source data. "Gary''s Student" wrote: The formula will work if the text starts with a day followed by a comma followed by a single space. Which is what your example shows. -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: Thanks for your prompt response, but I'm getting a #VALUE! result. Any ideas why this might be? "Gary''s Student" wrote: With your value in A1 use: =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1))) and format it as you choose -- Gary''s Student - gsnu200793 "Sarah (OGI)" wrote: I've received a report whereby column A shows a list of dates. Unfortunately, it seems that the date is written in a 'general' format and looks like this: Sunday, June 15, 2008 Is there an easy way to convert this into a date format, i.e. dd/mm/yy? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting date into text format | Excel Discussion (Misc queries) | |||
Converting data in General Format cell to a number | Excel Discussion (Misc queries) | |||
converting general text to date time | Excel Discussion (Misc queries) | |||
converting general cells format to text | Excel Discussion (Misc queries) | |||
Converting number or text to a Date Format | New Users to Excel |