Date format cannot change
 

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

Date format cannot change
 

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" wrote in message ...
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

Date format cannot change
 

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?

Date format cannot change
 

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?

Date format cannot change
 

On Tue, 12 Aug 2008 20:25:11 -0700, Learn-more
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

Date format cannot change
 

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" wrote in message ...
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?

Date format cannot change
 

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" wrote in message ...
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?

Date format cannot change
 

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" wrote in message ...
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?

Date format cannot change
 

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
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