Dates - Have Cake, Eat too

M

Manta.ray

I have a specific need to show 'month and year' and 'day, month and year' in
the same column. Because of multiple user input, I would like to make this
as simple as possible. If they enter 1/05, I want to see January, 2005 and
if the enter 1/10/05, I want to see January 10, 2005. From what I can see,
there is no way to format the entire column and acheive the stated goal. One
must choose one format and customize/reformat individual cells, which defeats
the purpose.

Any magic out there?
 
C

CLR

Maybe do this.........
Format column A as TEXT
Go ahead and enter 1/10, 1/10/05, etc etc down the column
In helper column B put this in B1 and copy down.........
=IF(LEN(A1)>=6,TEXT(A1,"mm/dd/yyyy"),TEXT(A1,"mm/dd"))

Vaya con Dios,
Chuck, CABGx3
 
P

Peo Sjoblom

You would need an event macro that returns a date, by only entering m/dd it
will default to
month = 1, day = 5 and current year so every time you enter let's say 04/05
it will return (assuming you do it today) 04/05/05 but if you put in 04/04
for Apr 2004 it will in fact return 04/04/05 so you can find a macro here
that will add date delimiters

http://www.cpearson.com/excel/DateTimeEntry.htm
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
If Len(.Value) - Len(Replace(.Value, "/", "")) = 1 Then
.NumberFormat = "mmmm, yyyy"
ElseIf Len(.Value) - Len(Replace(.Value, "/", "")) = 2 Then
.NumberFormat = "d mmmm, yyyy"
Else
.NumberFormat = "@"
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)
 
C

CLR

Sorry, I'm just learning to read, you see........<g>
This one is formatted more like what you asked for.........

=IF(LEN(A1)>=6,TEXT(A1,"mmmm dd, yyyy"),TEXT(A1," mmmm, yyyy"))

Vaya con Dios,
Chuck, CABGx3
 
Top