Date Formatting

[Guest]

Can anyone help me with a small problem I have with Date Fomatting?

I have an Excel based application where people need type the name of a month
into a cell, the only problem is that the cell isn't big enough to show the
entire month name for the longer months. I want to try to add some sort of
formatting so that if 'December' is entered for instance the cell is
automatically truncated to 'Dec'.
I can't seem to find anywhere in the formatting menu that solves this
problem, does anyone have a solution they could offer?

Thanks

Neil

 

[JulieD]

Hi Neil

as "december" typed into a cell isn't a "date" rather text you'll need to
use either autocorrect to change it to Dec - however, this will affect every
entry of December into any workbook or word document OR use a
worksheet_change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "December" Then Target.Value = "Dec"
End Sub

to use this, right mouse click on the sheet tab that you want this to happen
to and choose view code
now copy & paste this into the area on the right of the screen.
use ALT & F11 to switch back to your workbook to test.

Cheers
JulieD

 

[Don Guillett]

There are probably many ways to do this but how about a worksheet_change
event where
target.value=left(target,3)

 

[Jason Morin]

Why not just widen the column? Or as an alternative, you
could set up AutoCorrect entries (go to Tools >
AutoCorrect Options) and set up each month so that it's
replaced by its 3 char. abbreviation. For example,

Replace: December
With: Dec

HTH
Jason
Atlanta, GA

 

[Jason Morin]

Why not just widen the column? Or as an alternative, you
could set up AutoCorrect entries (go to Tools >
AutoCorrect Options) and set up each month so that it's
replaced by its 3 char. abbreviation. For example,

Replace: December
With: Dec

HTH
Jason
Atlanta, GA

 

[David McRitchie]

Setting up an AutoCorrect entry for this would cause nothing
but troubles in use of Office products. Don't do it.

Widening the column was specifically rejected in the original
post.

For Don's suggestion, you might as well make that
target.value = application.proper(left(target,3))

more information on event macros in
http://www.mvps.org/dmcritchie/excel/event.htm
be sure to limit the scope of the macro to a specific column(s),
and not update row 1 descriptions.

If you had the means of obtaining an actual date, you could
use custom cell formatting. mmm which would affect
display and not the value.

 

[Guest]

Thanks for everyones help on this one, I've decided to go the VBA route on
this one.

If anyone has any expertise on charts, I've posted a couple of questions
there too ;-)