Dear All,

I have a worksheet with some date in it like '21/09/2008 or some like
21/09/2008 without the '.

I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
when I check the format setting, it shows dd-mmm-yy but the display still
21/09/2008.

Further I tried control-H to find and replace the ' , but nothing found.

If I do it one by one, I can just use F2 and then just hit return, then the
format works ok to dd-mmm-yy.

I also tried copy to other location and paste it back etc, but still not
working.

Any idea why it is like that and not response to my format setting? Any way
in worksheet or VBA to change it and no need to do it one by one?

Any one had any suggestion?

Thanks a lot.
Learn-more

Hi Learn-more,

A simple technique to convert 'text' dates (or, in fact, any string of digits) to their serial/numerical values is to insert the
number 1 into any cell, copy that cell, then select the range to be converted and use Edit|Paste Special|Values|Multiply. You may
have to re-apply the date formatting afterwards, but that's about the extent of it.

--
Cheers
macropod
[MVP - Microsoft Word]


"Learn-more" <(E-Mail Removed)> wrote in message news:AA74B3AE-F1B5-4A19-9293-(E-Mail Removed)...
> Dear All,
>
> I have a worksheet with some date in it like '21/09/2008 or some like
> 21/09/2008 without the '.
>
> I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
> when I check the format setting, it shows dd-mmm-yy but the display still
> 21/09/2008.
>
> Further I tried control-H to find and replace the ' , but nothing found.
>
> If I do it one by one, I can just use F2 and then just hit return, then the
> format works ok to dd-mmm-yy.
>
> I also tried copy to other location and paste it back etc, but still not
> working.
>
> Any idea why it is like that and not response to my format setting? Any way
> in worksheet or VBA to change it and no need to do it one by one?
>
> Any one had any suggestion?
>
> Thanks a lot.
> Learn-more
>
>

I'm not an expert in this problem, but the initial " ' " tells Excel (I
think) that the data in this cell is text -- ie a character string -- and
therefore exempt from the formatting you set up for it; just as you can type
"Date" in a column header and it won't change even when you change the date
display of the entire column, a field preceded by " ' " acts the same way. I
think.

I've run into this kind of thing before, and the solution has always turned
out to be fairly simple but not very obvious...if I can just remember...
Stand by while I go experiment a bit.

....Ok, I don't remember how I did it before but this seems to work: Copy
the column of dates to a new blank column, using Edit.Paste Special, and
select the Add operation in the second section of the Paste-Special window.
Since the column you're "adding" the dates to is blank, it'll "add" them all
to 0 and therefore not change the values at all, but in the process it'll
convert them all to numbers again and you can format them as desired after
that.

I'm pretty sure I found another way to do it before, but it happens rarely
enough that I keep having to reinvent this one every time.

--- "Learn-more" wrote:
> I have a worksheet with some date in it like '21/09/2008 or some like
> 21/09/2008 without the '.
>
> I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
> when I check the format setting, it shows dd-mmm-yy but the display still
> 21/09/2008.
>
> Further I tried control-H to find and replace the ' , but nothing found.
>
> If I do it one by one, I can just use F2 and then just hit return, then the
> format works ok to dd-mmm-yy.
>
> I also tried copy to other location and paste it back etc, but still not
> working.
>
> Any idea why it is like that and not response to my format setting? Any way
> in worksheet or VBA to change it and no need to do it one by one?

Hi All,
Thanks both of you, I used both methods in worksheet and it works, however,
when I record it as Macro and run it, it doesn't work.

I don't know why VBA/Macro involving the Paste Special, Add or Multiply
doesnot work. I particularly use the Macro recorded to record the change and
then run the same thing but not work, especially when trying to format it .

Not sure why and need more work on it as I need a macro/VBA to change it.

Regards,
Learn-more


"Bob Bridges" wrote:

> I'm not an expert in this problem, but the initial " ' " tells Excel (I
> think) that the data in this cell is text -- ie a character string -- and
> therefore exempt from the formatting you set up for it; just as you can type
> "Date" in a column header and it won't change even when you change the date
> display of the entire column, a field preceded by " ' " acts the same way. I
> think.
>
> I've run into this kind of thing before, and the solution has always turned
> out to be fairly simple but not very obvious...if I can just remember...
> Stand by while I go experiment a bit.
>
> ...Ok, I don't remember how I did it before but this seems to work: Copy
> the column of dates to a new blank column, using Edit.Paste Special, and
> select the Add operation in the second section of the Paste-Special window.
> Since the column you're "adding" the dates to is blank, it'll "add" them all
> to 0 and therefore not change the values at all, but in the process it'll
> convert them all to numbers again and you can format them as desired after
> that.
>
> I'm pretty sure I found another way to do it before, but it happens rarely
> enough that I keep having to reinvent this one every time.
>
> --- "Learn-more" wrote:
> > I have a worksheet with some date in it like '21/09/2008 or some like
> > 21/09/2008 without the '.
> >
> > I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
> > when I check the format setting, it shows dd-mmm-yy but the display still
> > 21/09/2008.
> >
> > Further I tried control-H to find and replace the ' , but nothing found.
> >
> > If I do it one by one, I can just use F2 and then just hit return, then the
> > format works ok to dd-mmm-yy.
> >
> > I also tried copy to other location and paste it back etc, but still not
> > working.
> >
> > Any idea why it is like that and not response to my format setting? Any way
> > in worksheet or VBA to change it and no need to do it one by one?

On Tue, 12 Aug 2008 20:25:11 -0700, Learn-more
<(E-Mail Removed)> wrote:

>Dear All,
>
>I have a worksheet with some date in it like '21/09/2008 or some like
>21/09/2008 without the '.
>
>I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
>when I check the format setting, it shows dd-mmm-yy but the display still
>21/09/2008.
>
>Further I tried control-H to find and replace the ' , but nothing found.
>
>If I do it one by one, I can just use F2 and then just hit return, then the
>format works ok to dd-mmm-yy.
>
>I also tried copy to other location and paste it back etc, but still not
>working.
>
>Any idea why it is like that and not response to my format setting? Any way
>in worksheet or VBA to change it and no need to do it one by one?
>
>Any one had any suggestion?
>
>Thanks a lot.
>Learn-more
>

Others have explained the "why".
One other method to change this is to use the Data/Text-to-Columns wizard

Select the relevant cells. They need to be in the same column.

Then Data/Text to columns
Fixed Width
<Next>
Make sure there are no line breaks shown
<next>
Date: DMY
<Finish>

and that should convert them to Excel recognized dates, to which your format
should apply.

--ron

Hi Learn-more,

Try this:
Sub MakeDateValues()
Dim oCel As Range
For Each oCel In Selection.Cells
On Error Resume Next
oCel.Value = DateValue(oCel.Value)
Next
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


"Learn-more" <(E-Mail Removed)> wrote in message news:4F178E40-5C48-4A5F-8797-(E-Mail Removed)...
> Hi All,
> Thanks both of you, I used both methods in worksheet and it works, however,
> when I record it as Macro and run it, it doesn't work.
>
> I don't know why VBA/Macro involving the Paste Special, Add or Multiply
> doesnot work. I particularly use the Macro recorded to record the change and
> then run the same thing but not work, especially when trying to format it .
>
> Not sure why and need more work on it as I need a macro/VBA to change it.
>
> Regards,
> Learn-more
>
>
> "Bob Bridges" wrote:
>
>> I'm not an expert in this problem, but the initial " ' " tells Excel (I
>> think) that the data in this cell is text -- ie a character string -- and
>> therefore exempt from the formatting you set up for it; just as you can type
>> "Date" in a column header and it won't change even when you change the date
>> display of the entire column, a field preceded by " ' " acts the same way. I
>> think.
>>
>> I've run into this kind of thing before, and the solution has always turned
>> out to be fairly simple but not very obvious...if I can just remember...
>> Stand by while I go experiment a bit.
>>
>> ...Ok, I don't remember how I did it before but this seems to work: Copy
>> the column of dates to a new blank column, using Edit.Paste Special, and
>> select the Add operation in the second section of the Paste-Special window.
>> Since the column you're "adding" the dates to is blank, it'll "add" them all
>> to 0 and therefore not change the values at all, but in the process it'll
>> convert them all to numbers again and you can format them as desired after
>> that.
>>
>> I'm pretty sure I found another way to do it before, but it happens rarely
>> enough that I keep having to reinvent this one every time.
>>
>> --- "Learn-more" wrote:
>> > I have a worksheet with some date in it like '21/09/2008 or some like
>> > 21/09/2008 without the '.
>> >
>> > I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
>> > when I check the format setting, it shows dd-mmm-yy but the display still
>> > 21/09/2008.
>> >
>> > Further I tried control-H to find and replace the ' , but nothing found.
>> >
>> > If I do it one by one, I can just use F2 and then just hit return, then the
>> > format works ok to dd-mmm-yy.
>> >
>> > I also tried copy to other location and paste it back etc, but still not
>> > working.
>> >
>> > Any idea why it is like that and not response to my format setting? Any way
>> > in worksheet or VBA to change it and no need to do it one by one?

And here's a more robust form that won't convert fractions & other three character, 2-digit strings with a '/', ' ' or '-' in
between:
Sub MakeDateValues()
Dim oCel As Range
Dim i As Integer
Dim SepStr As String
SepStr = "/, ,-"
For Each oCel In Selection.Cells
For i = 0 To UBound(Split(SepStr, ","))
On Error Resume Next
If Len(Replace(oCel.Value, Split(SepStr, ",")(i), "")) = _
Len(oCel.Value) - 2 Then oCel.Value = DateValue(oCel.Value)
Next
Next
End Sub

If you've got other separator characters, just add them to the SepStr string, with commas in between,

--
Cheers
macropod
[MVP - Microsoft Word]


"Learn-more" <(E-Mail Removed)> wrote in message news:4F178E40-5C48-4A5F-8797-(E-Mail Removed)...
> Hi All,
> Thanks both of you, I used both methods in worksheet and it works, however,
> when I record it as Macro and run it, it doesn't work.
>
> I don't know why VBA/Macro involving the Paste Special, Add or Multiply
> doesnot work. I particularly use the Macro recorded to record the change and
> then run the same thing but not work, especially when trying to format it .
>
> Not sure why and need more work on it as I need a macro/VBA to change it.
>
> Regards,
> Learn-more
>
>
> "Bob Bridges" wrote:
>
>> I'm not an expert in this problem, but the initial " ' " tells Excel (I
>> think) that the data in this cell is text -- ie a character string -- and
>> therefore exempt from the formatting you set up for it; just as you can type
>> "Date" in a column header and it won't change even when you change the date
>> display of the entire column, a field preceded by " ' " acts the same way. I
>> think.
>>
>> I've run into this kind of thing before, and the solution has always turned
>> out to be fairly simple but not very obvious...if I can just remember...
>> Stand by while I go experiment a bit.
>>
>> ...Ok, I don't remember how I did it before but this seems to work: Copy
>> the column of dates to a new blank column, using Edit.Paste Special, and
>> select the Add operation in the second section of the Paste-Special window.
>> Since the column you're "adding" the dates to is blank, it'll "add" them all
>> to 0 and therefore not change the values at all, but in the process it'll
>> convert them all to numbers again and you can format them as desired after
>> that.
>>
>> I'm pretty sure I found another way to do it before, but it happens rarely
>> enough that I keep having to reinvent this one every time.
>>
>> --- "Learn-more" wrote:
>> > I have a worksheet with some date in it like '21/09/2008 or some like
>> > 21/09/2008 without the '.
>> >
>> > I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
>> > when I check the format setting, it shows dd-mmm-yy but the display still
>> > 21/09/2008.
>> >
>> > Further I tried control-H to find and replace the ' , but nothing found.
>> >
>> > If I do it one by one, I can just use F2 and then just hit return, then the
>> > format works ok to dd-mmm-yy.
>> >
>> > I also tried copy to other location and paste it back etc, but still not
>> > working.
>> >
>> > Any idea why it is like that and not response to my format setting? Any way
>> > in worksheet or VBA to change it and no need to do it one by one?

Hi macropod,

I tried that and it works well in VBA, so no need to do it one by one in the
worksheet.

Thanks.
Learn-more

"macropod" wrote:

> Hi Learn-more,
>
> Try this:
> Sub MakeDateValues()
> Dim oCel As Range
> For Each oCel In Selection.Cells
> On Error Resume Next
> oCel.Value = DateValue(oCel.Value)
> Next
> End Sub
>
> --
> Cheers
> macropod
> [MVP - Microsoft Word]
>
>
> "Learn-more" <(E-Mail Removed)> wrote in message news:4F178E40-5C48-4A5F-8797-(E-Mail Removed)...
> > Hi All,
> > Thanks both of you, I used both methods in worksheet and it works, however,
> > when I record it as Macro and run it, it doesn't work.
> >
> > I don't know why VBA/Macro involving the Paste Special, Add or Multiply
> > doesnot work. I particularly use the Macro recorded to record the change and
> > then run the same thing but not work, especially when trying to format it .
> >
> > Not sure why and need more work on it as I need a macro/VBA to change it.
> >
> > Regards,
> > Learn-more
> >
> >
> > "Bob Bridges" wrote:
> >
> >> I'm not an expert in this problem, but the initial " ' " tells Excel (I
> >> think) that the data in this cell is text -- ie a character string -- and
> >> therefore exempt from the formatting you set up for it; just as you can type
> >> "Date" in a column header and it won't change even when you change the date
> >> display of the entire column, a field preceded by " ' " acts the same way. I
> >> think.
> >>
> >> I've run into this kind of thing before, and the solution has always turned
> >> out to be fairly simple but not very obvious...if I can just remember...
> >> Stand by while I go experiment a bit.
> >>
> >> ...Ok, I don't remember how I did it before but this seems to work: Copy
> >> the column of dates to a new blank column, using Edit.Paste Special, and
> >> select the Add operation in the second section of the Paste-Special window.
> >> Since the column you're "adding" the dates to is blank, it'll "add" them all
> >> to 0 and therefore not change the values at all, but in the process it'll
> >> convert them all to numbers again and you can format them as desired after
> >> that.
> >>
> >> I'm pretty sure I found another way to do it before, but it happens rarely
> >> enough that I keep having to reinvent this one every time.
> >>
> >> --- "Learn-more" wrote:
> >> > I have a worksheet with some date in it like '21/09/2008 or some like
> >> > 21/09/2008 without the '.
> >> >
> >> > I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
> >> > when I check the format setting, it shows dd-mmm-yy but the display still
> >> > 21/09/2008.
> >> >
> >> > Further I tried control-H to find and replace the ' , but nothing found.
> >> >
> >> > If I do it one by one, I can just use F2 and then just hit return, then the
> >> > format works ok to dd-mmm-yy.
> >> >
> >> > I also tried copy to other location and paste it back etc, but still not
> >> > working.
> >> >
> >> > Any idea why it is like that and not response to my format setting? Any way
> >> > in worksheet or VBA to change it and no need to do it one by one?
>

Hi Ron,

I tried the Data/Text-to-Columns wizard, it works but need to be in the
worksheet. It still help when change in range. Thanks a lot.

Further, macropod above suggested a VBA that also work well too.

Thanks everyone for all the help.

Learn-more


"Ron Rosenfeld" wrote:

> On Tue, 12 Aug 2008 20:25:11 -0700, Learn-more
> <(E-Mail Removed)> wrote:
>
> >Dear All,
> >
> >I have a worksheet with some date in it like '21/09/2008 or some like
> >21/09/2008 without the '.
> >
> >I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
> >when I check the format setting, it shows dd-mmm-yy but the display still
> >21/09/2008.
> >
> >Further I tried control-H to find and replace the ' , but nothing found.
> >
> >If I do it one by one, I can just use F2 and then just hit return, then the
> >format works ok to dd-mmm-yy.
> >
> >I also tried copy to other location and paste it back etc, but still not
> >working.
> >
> >Any idea why it is like that and not response to my format setting? Any way
> >in worksheet or VBA to change it and no need to do it one by one?
> >
> >Any one had any suggestion?
> >
> >Thanks a lot.
> >Learn-more
> >
>
> Others have explained the "why".
> One other method to change this is to use the Data/Text-to-Columns wizard
>
> Select the relevant cells. They need to be in the same column.
>
> Then Data/Text to columns
> Fixed Width
> <Next>
> Make sure there are no line breaks shown
> <next>
> Date: DMY
> <Finish>
>
> and that should convert them to Excel recognized dates, to which your format
> should apply.
>
> --ron
>