Date display

J

Joel

Hi can you help?

I would like to display a date like the example that follows. M 18.09 I
would like to show the first intial of the day for example M for Monday
followed by the date in numbers like 18 and then the month also in numbers
like 09 not bothered about the year

Thanks Joel
 
B

Bob Phillips

If it can be in another cell then

=LEFT(TEXT(A1,"ddd"))&" "&TEXT(A1,"dd\.mm")

If you want it as a format, not sure that is doable.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

Roger Govier

Hi Joel

Format>Cells>Number>Custom> ddd dd.mm
will produce Mon 18.09
which can still be used as a date in further calculations.

To get what you asked for, and with the date in A1, use
=LEFT(TEXT(A1,"ddd"))&" "&TEXT(A1,"dd.mm")
but note that the result is a text value and cannot be used in any further
calculation.
 
R

Rick Rothstein \(MVP - VB\)

If you don't mind using a worksheet event, the following will convert an
entered date in a cell into the format you asked for (but the entry will be
text afterwards)...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If IsDate(Target.Value) Then Target.Value = _
Left(Format(Target.Value, "ddd"), 1) & _
Format(Target.Value, " dd.mm")
Whoops:
Application.EnableEvents = True
End Sub

If this is unfamiliar to you, simply right-click on the worksheet tab for
the worksheet you want this functionality on and select View Code from the
popup menu that appears; then copy/paste the above code into the window that
appeared. When you go back to the worksheet, any cell that you enter a date
into will have that date converted to the format you indicated. If you want
to restrict this functionality, use the code below and specify the range it
should operate over in the Range function call in the first code line where
I placed "A:A" (for example purposes - it restricts the functionality to
Column A)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
On Error GoTo Whoops
Application.EnableEvents = False
If IsDate(Target.Value) Then Target.Value = _
Left(Format(Target.Value, "ddd"), 1) & _
Format(Target.Value, " dd.mm")
Whoops:
Application.EnableEvents = True
End Sub

Rick
 
Top