|
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 |
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 |
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 |
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 |
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 |
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 |
Convert test to date
Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell
and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 |
Convert test to date
Maybe it's time to report back the exact steps you used when you tried it.
GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson |
Convert test to date
OK..........
Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson |
Convert test to date
I can't get it to fail.
GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson |
Convert test to date
Try using the same destination cell (use A1 in your example).
Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert test to date
Same thing. I have actually tried this on 2 computers, work and home, with
the same results. I wonder if there is some setting that I don't have turned on or something. Does it matter what format (text, general, number, etc) the source cell (A1) is? "Dave Peterson" wrote: Try using the same destination cell (use A1 in your example). Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert test to date
I've never had a problem when the cell was text or general.
What is the format of the original cell? Is the value text or numeric? (use =isnumber(a1) to test) What version of excel are you using? GKW in GA wrote: Same thing. I have actually tried this on 2 computers, work and home, with the same results. I wonder if there is some setting that I don't have turned on or something. Does it matter what format (text, general, number, etc) the source cell (A1) is? "Dave Peterson" wrote: Try using the same destination cell (use A1 in your example). Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert test to date
General.
I have Microsoft Office Excel 2003 SP2 Part of MS Office Standard Edition "Dave Peterson" wrote: I've never had a problem when the cell was text or general. What is the format of the original cell? Is the value text or numeric? (use =isnumber(a1) to test) What version of excel are you using? GKW in GA wrote: Same thing. I have actually tried this on 2 computers, work and home, with the same results. I wonder if there is some setting that I don't have turned on or something. Does it matter what format (text, general, number, etc) the source cell (A1) is? "Dave Peterson" wrote: Try using the same destination cell (use A1 in your example). Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert test to date
Is the value text or numeric? (use =isnumber(a1) to test)
I still can't duplicate your problem. How about another test: What do you see when you use this formula: =len(a1) GKW in GA wrote: General. I have Microsoft Office Excel 2003 SP2 Part of MS Office Standard Edition "Dave Peterson" wrote: I've never had a problem when the cell was text or general. What is the format of the original cell? Is the value text or numeric? (use =isnumber(a1) to test) What version of excel are you using? GKW in GA wrote: Same thing. I have actually tried this on 2 computers, work and home, with the same results. I wonder if there is some setting that I don't have turned on or something. Does it matter what format (text, general, number, etc) the source cell (A1) is? "Dave Peterson" wrote: Try using the same destination cell (use A1 in your example). Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert test to date
isnumber(a1) = TRUE
len(a1) = 8 "Dave Peterson" wrote: Is the value text or numeric? (use =isnumber(a1) to test) I still can't duplicate your problem. How about another test: What do you see when you use this formula: =len(a1) GKW in GA wrote: General. I have Microsoft Office Excel 2003 SP2 Part of MS Office Standard Edition "Dave Peterson" wrote: I've never had a problem when the cell was text or general. What is the format of the original cell? Is the value text or numeric? (use =isnumber(a1) to test) What version of excel are you using? GKW in GA wrote: Same thing. I have actually tried this on 2 computers, work and home, with the same results. I wonder if there is some setting that I don't have turned on or something. Does it matter what format (text, general, number, etc) the source cell (A1) is? "Dave Peterson" wrote: Try using the same destination cell (use A1 in your example). Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert test to date
And you see the same thing in the formula bar as before 20071225???
I don't have any more guesses. If you find a solution, please post back. GKW in GA wrote: isnumber(a1) = TRUE len(a1) = 8 "Dave Peterson" wrote: Is the value text or numeric? (use =isnumber(a1) to test) I still can't duplicate your problem. How about another test: What do you see when you use this formula: =len(a1) GKW in GA wrote: General. I have Microsoft Office Excel 2003 SP2 Part of MS Office Standard Edition "Dave Peterson" wrote: I've never had a problem when the cell was text or general. What is the format of the original cell? Is the value text or numeric? (use =isnumber(a1) to test) What version of excel are you using? GKW in GA wrote: Same thing. I have actually tried this on 2 computers, work and home, with the same results. I wonder if there is some setting that I don't have turned on or something. Does it matter what format (text, general, number, etc) the source cell (A1) is? "Dave Peterson" wrote: Try using the same destination cell (use A1 in your example). Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert test to date
Have you tried converting with a formula:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) "Dave Peterson" wrote in message ... And you see the same thing in the formula bar as before 20071225??? I don't have any more guesses. If you find a solution, please post back. GKW in GA wrote: isnumber(a1) = TRUE len(a1) = 8 "Dave Peterson" wrote: Is the value text or numeric? (use =isnumber(a1) to test) I still can't duplicate your problem. How about another test: What do you see when you use this formula: =len(a1) GKW in GA wrote: General. I have Microsoft Office Excel 2003 SP2 Part of MS Office Standard Edition "Dave Peterson" wrote: I've never had a problem when the cell was text or general. What is the format of the original cell? Is the value text or numeric? (use =isnumber(a1) to test) What version of excel are you using? GKW in GA wrote: Same thing. I have actually tried this on 2 computers, work and home, with the same results. I wonder if there is some setting that I don't have turned on or something. Does it matter what format (text, general, number, etc) the source cell (A1) is? "Dave Peterson" wrote: Try using the same destination cell (use A1 in your example). Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert test to date
yep, same thing. It doesn't matter whether I specify YMD or MDY in the drop
down box, 20071225 still yields 20071225. Thanks for trying, though. "Dave Peterson" wrote: And you see the same thing in the formula bar as before 20071225??? I don't have any more guesses. If you find a solution, please post back. GKW in GA wrote: isnumber(a1) = TRUE len(a1) = 8 "Dave Peterson" wrote: Is the value text or numeric? (use =isnumber(a1) to test) I still can't duplicate your problem. How about another test: What do you see when you use this formula: =len(a1) GKW in GA wrote: General. I have Microsoft Office Excel 2003 SP2 Part of MS Office Standard Edition "Dave Peterson" wrote: I've never had a problem when the cell was text or general. What is the format of the original cell? Is the value text or numeric? (use =isnumber(a1) to test) What version of excel are you using? GKW in GA wrote: Same thing. I have actually tried this on 2 computers, work and home, with the same results. I wonder if there is some setting that I don't have turned on or something. Does it matter what format (text, general, number, etc) the source cell (A1) is? "Dave Peterson" wrote: Try using the same destination cell (use A1 in your example). Dave Peterson wrote: I can't get it to fail. GKW in GA wrote: OK.......... Key in 20071225 in cell A1. Select that cell (A1) and go to DATA | TEXT TO COLUMNS Select the FIXED WIDTH radio button and click NEXT Click NEXT on STEP 2 window Under COLUMN DATA FORMAT, select DATE radio button and select YMD from the drop down box. In the DESTINATION text box, change the cell to something else beside $A$1 (or blank out the text box and select the cell you want to be the destination Click FINISH Presto.......the destination cell contains 20071225, same as A1. Note: A1 is format GENERAL I've tried it seelcting MDY and others from drop down box "Dave Peterson" wrote: Maybe it's time to report back the exact steps you used when you tried it. GKW in GA wrote: Sorry to report, but I tried a blank work book, keyed in 20071225 into a cell and tried what you said and got the same results.......nothing Maybe I have some setting turned off or I don't have the latest update or something. "Ron Coderre" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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 |
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 |
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 |
| All times are GMT +1. The time now is 10:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com