| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert test to date
I have a cell, F5, that has 20071225 in it. I want to convert this to a date
field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert test to date
Try this:
Select your column of "dates" From the Excel Main Menu: <data<text-to-columns Click [Next] Click [Next] Check: Date.....YMD Click [Finish] Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... I have a cell, F5, that has 20071225 in it. I want to convert this to a date field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert test to date
no, I just end up with 20071225, same as the source cell
"Ron Coderre" wrote: Try this: Select your column of "dates" From the Excel Main Menu: <data<text-to-columns Click [Next] Click [Next] Check: Date.....YMD Click [Finish] Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... I have a cell, F5, that has 20071225 in it. I want to convert this to a date field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert test to date
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
-------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... no, I just end up with 20071225, same as the source cell "Ron Coderre" wrote: Try this: Select your column of "dates" From the Excel Main Menu: <data<text-to-columns Click [Next] Click [Next] Check: Date.....YMD Click [Finish] Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... I have a cell, F5, that has 20071225 in it. I want to convert this to a date field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert test to date
yes, I select the field that has the 20071225 and then select data/text to
columns and follow the instructions you listed, choosing DATe..YMD "Ron Coderre" wrote: Are you *sure* you're setting the field to YMD.....(Year Month Day)? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... no, I just end up with 20071225, same as the source cell "Ron Coderre" wrote: Try this: Select your column of "dates" From the Excel Main Menu: <data<text-to-columns Click [Next] Click [Next] Check: Date.....YMD Click [Finish] Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... I have a cell, F5, that has 20071225 in it. I want to convert this to a date field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert test to date
OK....Let's troubleshoot.
In a new worksheet, enter 20071225 in a column of cells. Then try the method I posted. Does that work? If yes.... Go to your "real" data and replace one of the values by entering 20071225 in one of the cells Try the method again.... Does only THAT cell become a date? If yes...something's going on with your data... If no....Then I'm puzzled. Let us know what you discover. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... yes, I select the field that has the 20071225 and then select data/text to columns and follow the instructions you listed, choosing DATe..YMD "Ron Coderre" wrote: Are you *sure* you're setting the field to YMD.....(Year Month Day)? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... no, I just end up with 20071225, same as the source cell "Ron Coderre" wrote: Try this: Select your column of "dates" From the Excel Main Menu: <data<text-to-columns Click [Next] Click [Next] Check: Date.....YMD Click [Finish] Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "GKW in GA" wrote in message ... I have a cell, F5, that has 20071225 in it. I want to convert this to a date field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert test to date
On Tue, 11 Dec 2007 12:03:00 -0800, GKW in GA
wrote: I have a cell, F5, that has 20071225 in it. I want to convert this to a date field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating This is very strange. With your value in A1, what is the result of this formula: =DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100)) ?? --ron |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert test to date
Using your formula below, 20071225 yields 12/25/2007
"Ron Rosenfeld" wrote: On Tue, 11 Dec 2007 12:03:00 -0800, GKW in GA wrote: I have a cell, F5, that has 20071225 in it. I want to convert this to a date field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating This is very strange. With your value in A1, what is the result of this formula: =DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100)) ?? --ron |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert test to date
On Thu, 13 Dec 2007 08:23:02 -0800, GKW in GA
wrote: Using your formula below, 20071225 yields 12/25/2007 "Ron Rosenfeld" wrote: On Tue, 11 Dec 2007 12:03:00 -0800, GKW in GA wrote: I have a cell, F5, that has 20071225 in it. I want to convert this to a date field that displays as 12/25/2007 When I use the function =DATEVALUE(F5) , I get #VALUE! Can you tell me how to do this without using substringing and concantenating This is very strange. With your value in A1, what is the result of this formula: =DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100)) ?? --ron Well, at least that gives you a method of converting the date. But I sure don't understand why the Data/Text-to-Columns wizard fails. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| test to Julian date | Excel Discussion (Misc queries) | |||
| Using a logical test on a date is failing | Excel Worksheet Functions | |||
| Calculate mean of test scores from rows of test answers | Excel Discussion (Misc queries) | |||
| Test plus date and time | Excel Worksheet Functions | |||
| test for date in column question | Excel Worksheet Functions | |||
Hybrid Mode
