Date Formatting

[Guest]

Hello Fellow Excellers.
When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
yyyy, so that it would read Monday 1 January 2006, then the wrap text
facility does not work.
Is there any way to put in an 'alt-enter' type character so that I could
have the Monday on the first line and the rest underneath.

 

[Max]

Not exactly what you're after, but perhaps worth a try
(Hang around for views from others ..)

Assuming dates entered in A1 down

Put in B1:
=TEXT(A1,"dddd")&CHAR(10)&TEXT(A1,"dd mmmm yyyy")
Format B1 to wrap text & copy B1 down
Col B will return the desired display

 

[Guest]

Many thanks.
This is a lot further than I would of got.
I would still like a solution if possible for the way described, as I would
like to use it in other items, but your formula does work on this occasion.

I am forever grateful and in your debt.

 

[Max]

You're welcome !

As mentioned in the response,
do hang around awhile for views from others.

There just might be a way to do it directly (perhaps via vba ?)
that others may step-in to offer you.

 

[Bob Phillips]

One way

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsDate(.Value) Then
If .NumberFormat = "dddd dd mmmm yyyy" Then
.Value = Format(.Value, "dddd " & vbLf & "dd mmmm yyyy")
End If
End If
End With
End If

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

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Dear Bob
I'm afraid that I have never got to grips with any form of VBA before. I
have followed the on screen insructions and pasted the code into the sheet1
tab, but where do I go from there.
What I have done is simply pasted the code in, closed the screen, and
entered a date in H1 but the wrap is not working. I have still formatted the
cell as wrap text but this did not help. I know that I am going wrong
somewhere, but I know that you are the one to guide me through.

Best Regards

 

[Dave Peterson]

You can use format|cells|number tab

Give it a custom format of:
dddd(alt-0010)dd mmmm yyyy

Hit and hold the alt key while typing 0010 on the numeric keypad--not above the
QWERTY keys.

And turn on wrap text
format|cells|alignment tab

But be aware that you'll have to adjust the rowheight yourself (autofitting
won't work).

And if you make the cell too narrow for the whole string (not after wrapping
text), you'll see ###'s.

====
Another format to consider:
dddd* dd mmmm yyyy
This works pretty neat when you widen the column.

 

[Roger Govier]

Hi Rick

Copy the following, then right click on Worksheet Tab, Select View code
and Paste into the white pane.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row <2 Then Exit Sub
If Target.Column > 1 Then Exit Sub

If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "A")
.Value = Format(.Value, "dddd" & vbLf & "d mmmm yyyy")
End With
Application.EnableEvents = True
End If
End With
End Sub

This assumes that you want all entries in Column A to be adjusted.
If you want a different column, change "A"'s to the column Letter
required, change Target.Column number to <> and the column number for
the entry.
Target.row is set to ignore row 1, assuming this is your header, change
as appropriate.

 

[Bob Phillips]

If you copied the code to the sheet module as described, you should have got
the two lines as required. It worked fine in my tests.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Roger Govier]

Hey, that's quite sneaky Dave.
I would never have thought about entering control characters into the
number format.
Very effective.

 

[Guest]

Dear Bob, Max, Dave & Roger
I am forever grateful for all your time, effort and patience for someone who
has to have something drummed in before it sticks in my mind.
I did get Rogers code to work, I couldn't with Bobs', although I sure that
it is because a complete amateur is trying.
If anyone of you are ever in England, then the drinks are on me !

Regards

 

[Roger Govier]

Hi Big Rick

If anyone of you are ever in England, then the drinks are on me !

Be careful there,
Bob is in Dorset, I am in Wales (but often across the border)
Don't know about Bob, but I can drink quite a lot of beer!! <vbg>

 

[Guest]

I work at a swimming pool in Blackpool.
There are 300,000 gallons of 'drink' to go at !!

 

[Dave Peterson]

One of the nicer things about using format is that the value stays a date, too.
So it makes doing date arithmetic a little easier if you need it later.

 

[Roger Govier]

Hi Big Rick

I won't beat a trail up the M6 too quickly then<g>
--
Regards

Roger Govier

I work at a swimming pool in Blackpool.
There are 300,000 gallons of 'drink' to go at !!

 

[Bob Phillips]

Roger,

You know that all Brits can drink! The world is astounded at our capacity.

Bob

 

[Roger Govier]

Hi Bob

Then before too long, I must travel down to your neck of the woods and
enjoy a pint of Badger with you.

--
Regards

Roger Govier

Roger,

You know that all Brits can drink! The world is astounded at our
capacity.

Bob

 

[Bob Phillips]

It is good that it keeps the original value, but the fact that you have to
allow cell width to cater for the full text string nullifies the usefulness
of this technique IMO.

Bob

 

[Dave Peterson]

I'm not sure I'd say nullify. I (still) think that it depends on the
circumstance. If this were a header for a column full of wide descriptions,
then I wouldn't care at all.

It is good that it keeps the original value, but the fact that you have to
allow cell width to cater for the full text string nullifies the usefulness
of this technique IMO.

Bob

 

[via135]

fantastic indeed!!!

-via135

Hi Big Rick

I won't beat a trail up the M6 too quickly then<g>