Convert test to date

's Computer Specifications

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

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
>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
>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
> news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
> >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
> >
>
>
>

's Computer Specifications

Are you *sure* you're setting the field to YMD.....(Year Month Day)?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"GKW in GA" <(E-Mail Removed)> wrote in message
news:60E8F8F6-36F0-4149-B091-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
>> >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
>> >
>>
>>
>>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
> news:60E8F8F6-36F0-4149-B091-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:3892441A-1D15-4C99-A3B9-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:60E8F8F6-36F0-4149-B091-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
>> >> >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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
> news:3892441A-1D15-4C99-A3B9-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:60E8F8F6-36F0-4149-B091-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
> >> >> >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
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
> > news:3892441A-1D15-4C99-A3B9-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > >> news:60E8F8F6-36F0-4149-B091-(E-Mail Removed)...
> > >> > 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" <(E-Mail Removed)> wrote in message
> > >> >> news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
> > >> >> >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

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
> > > news:3892441A-1D15-4C99-A3B9-(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > >> news:60E8F8F6-36F0-4149-B091-(E-Mail Removed)...
> > > >> > 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" <(E-Mail Removed)> wrote in message
> > > >> >> news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
> > > >> >> >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
>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
> > > > news:3892441A-1D15-4C99-A3B9-(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > >> news:60E8F8F6-36F0-4149-B091-(E-Mail Removed)...
> > > > >> > 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" <(E-Mail Removed)> wrote in message
> > > > >> >> news:6FEC96D4-0F7C-4EB9-84FC-(E-Mail Removed)...
> > > > >> >> >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

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Convert date (m/dd/yyyy) in text format into an excel recognised date	Rob P	Microsoft Excel Programming	3	30th Apr 2010 12:40 AM
Most recent date prior to test date.	Skip	Microsoft Access Queries	1	21st Oct 2009 10:19 PM
in a query test on date to see if it falls between two date date?	=?Utf-8?B?ZGF0ZSBjYWxjIGluIHF1ZXJ5IGluIGFjY2VzcyAy	Microsoft Access Queries	1	1st Jun 2006 04:34 AM
convert test field to date field and combine	=?Utf-8?B?Um9u?=	Microsoft Access	0	10th Jun 2004 02:26 AM
how do you convert formulas to just plain test?	craig	Microsoft Excel Misc	2	4th Mar 2004 12:15 AM