Ron Coderre said: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 said: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
GKW in GA said:no, I just end up with 20071225, same as the source cell
Ron Coderre said: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 said: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
Ron Coderre said:Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
GKW in GA said:no, I just end up with 20071225, same as the source cell
Ron Coderre said: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)
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
GKW in GA said: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 said:Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
GKW in GA said:no, I just end up with 20071225, same as the source cell
:
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)
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
Ron Coderre said: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 said: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 said:Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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
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 said: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 said: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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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
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
Dave Peterson said:Maybe it's time to report back the exact steps you used when you tried it.
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 said: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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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
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 said:Maybe it's time to report back the exact steps you used when you tried it.
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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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 said:I can't get it to fail.
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 said: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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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
Try using the same destination cell (use A1 in your example).
Dave said:I can't get it to fail.
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
:
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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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
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
Try using the same destination cell (use A1 in your example).
Dave said: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
:
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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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 said: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?
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
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
:
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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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
General.
I have Microsoft Office Excel 2003 SP2
Part of MS Office Standard Edition
Dave Peterson said: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?
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?
:
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
:
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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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 said: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)
General.
I have Microsoft Office Excel 2003 SP2
Part of MS Office Standard Edition
Dave Peterson said: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?
:
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
:
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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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
isnumber(a1) = TRUE
len(a1) = 8
Dave Peterson said: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)
General.
I have Microsoft Office Excel 2003 SP2
Part of MS Office Standard Edition
:
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?
:
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
:
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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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 said: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.
isnumber(a1) = TRUE
len(a1) = 8
Dave Peterson said: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
:
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?
:
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
:
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.
:
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)
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
:
Are you *sure* you're setting the field to
YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"GKW in GA" <[email protected]>
wrote in message
no, I just end up with 20071225, same as the
source cell
:
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" <[email protected]>
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 said: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.
isnumber(a1) = TRUE
len(a1) = 8
Dave Peterson said: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
:
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?
:
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
:
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.
:
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)
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
:
Are you *sure* you're setting the field to YMD.....(Year Month Day)?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
no, I just end up with 20071225, same as the source cell
:
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)
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
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
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.