Date Formatting

[Michael]

Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER my
dates as MMM-YY. The problem is that if I enter Feb-07 (for me, February
2007), Excel treats that as February 7th, 2009. Is there anyway I can force
Excel to recognize my date entry as MMM-YY?

Thanks,

Michael

 

[Rick Rothstein]

First off, Feb-07 is not a date... a date contains a month, DAY and year.
You can force Excel to treat Feb-07 as a date by supplying a day value. If
you don't want to type it in, then you will need some VB event code to force
the conversion to an actual date. Is a VB solution acceptable?

 

[Michael]

Understood ... However, when I type Feb-07, Excel THINKS it is a date and
converts to a date format of DD-MMM. I was hoping to intercept that logic ON
ENTER and convert to MMM-YY. I guess that is where the VBA comes into play.
I'll just get used to entering the full date. Thnaks.

Michael

 

[Rick Rothstein]

Correct... VBA would be needed to incept the Feb-07 entry and change to a
date that you could format the way you want BEFORE Excel had a chance to
mangle it in a way you didn't want.

 

[Dave Peterson]

But remember that excel will make it a real date (if it sees it as a date)
before any event code will fire.

You may want to either enter the values as real dates or format your input range
as Text and have the event macro parse the value to what you want.

 

[Rick Rothstein]

I was thinking of formatting the cell as text via code in SelectionChange
event (to handle that problem) and then reformatting it in the Change event
once I had changed the entry to one that would satisfy the OP's requirement.

 

[Shane Devenshire]

Hi,

Actually, Feb-07 is considered a date by Excel. It automatically assumes
the current year and makes your entry Feb 7 2009 or in Excel's style 2/7/09.

Here is one workaround - enter Feb-2007 that will be treated as 2/1/2007.

On the other hand if you really don't want a date here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:A10"))
If Not isect Is Nothing Then
On Error GoTo ErrorHandler
Application.EnableEvents = False
Target = DateValue("" & Month(Target) & "-1-" & Day(Target) & "")
Target.NumberFormat = "MMM-YY"
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.

 

[Rick Rothstein]

One thing that may speak against using your event code... enter Feb-07 into
the cell, hit Enter, re-select the cell, click into the formula bar and then
hit the Enter key.

I was thinking of event code along these lines (which is immune to the above
problem)...

'*************** START OF CODE ***************
Dim OldFormat As String
Dim OldAddress As String

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not Intersect(Target, Range("A1:A9")) Is Nothing And .Count = 1 Then
Application.EnableEvents = False
On Error GoTo Whoops
If IsDate("28-" & .Value) Then
.NumberFormat = "mmm-yy"
.Value = CDate("28-" & .Value) - 27
OldFormat = "mmm-yy"
ElseIf IsDate(.Value) Then
.NumberFormat = "mmm-yy"
.Value = CDate(.Value)
OldFormat = "mmm-yy"
End If
End If
End With
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If OldAddress <> "" Then Range(OldAddress).NumberFormat = OldFormat
OldAddress = Target.Address
OldFormat = Target.NumberFormat
If Not Intersect(Target, Range("A1:A9")) Is Nothing Then
Target.NumberFormat = "@"
End If
End Sub
'*************** END OF CODE ***************

The only anomaly I see with this code is that clicking back into a cell in
the target range converts the entry to its serial number value until you
leave the cell or change its contents.

 

[Dave Peterson]

There are some things that seem too much like magic for me. I wouldn't use an
event. I'd learn to enter the value as a real date.

That may not be what the OP wants, though <vbg>.

 

[Rick Rothstein]

I posted the code I was thinking about in response to Shane's posting if you
are interested in seeing what I had in mind. By the way, I agree with you on
just entering a correct date to begin with (just two additional characters
would do it).

 

[Dave Peterson]

All that to save 2 characters of typing doesn't seem worth it to me.

And event macros have that clearing the clipboard problem that can make it a
nonstarter for lots of things.

I posted the code I was thinking about in response to Shane's posting if you
are interested in seeing what I had in mind. By the way, I agree with you on
just entering a correct date to begin with (just two additional characters
would do it).

 

[Rick Rothstein]

All that to save 2 characters of typing doesn't seem worth it to me.

No argument from me on that one.

And event macros have that clearing the clipboard problem that can make it
a
nonstarter for lots of things.

I haven't looked into it, but I would guess the clipboard could be protected
(well, copied and then replaced) using some API function calls. I'll have to
look into that idea one of these days.

 

[Michael]

Thanks folks - I think I'll just get used to entering the full date.
Interesting thoughts on the subject, though.

Michael