If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Formatting
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. -- Your help is and always has been very much appreciated. Thanking you in anticipation. --- --- --- Big Rick |
#2
|
|||
|
|||
Date Formatting
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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Big Rick" wrote in message ... 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. -- Your help is and always has been very much appreciated. Thanking you in anticipation. --- --- --- Big Rick |
#3
|
|||
|
|||
Date Formatting
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. -- Big Rick "Max" wrote: 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Big Rick" wrote in message ... 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. -- Your help is and always has been very much appreciated. Thanking you in anticipation. --- --- --- Big Rick |
#4
|
|||
|
|||
Date Formatting
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. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Big Rick" wrote in message ... 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. -- Big Rick |
#5
|
|||
|
|||
Date Formatting
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) "Max" wrote in message ... 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. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Big Rick" wrote in message ... 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. -- Big Rick |
#6
|
|||
|
|||
Date Formatting
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 -- -- Big Rick "Bob Phillips" wrote: 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 |
#7
|
|||
|
|||
Date Formatting
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. Big Rick wrote: 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. -- Your help is and always has been very much appreciated. Thanking you in anticipation. --- --- --- Big Rick -- Dave Peterson |
#8
|
|||
|
|||
Date Formatting
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. -- Regards Roger Govier "Max" wrote in message ... 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. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Big Rick" wrote in message ... 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. -- Big Rick |
#9
|
|||
|
|||
Date Formatting
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) "Big Rick" wrote in message ... 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 -- -- Big Rick "Bob Phillips" wrote: 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 |
#10
|
|||
|
|||
Date Formatting
Hey, that's quite sneaky Dave.
I would never have thought about entering control characters into the number format. Very effective. -- Regards Roger Govier "Dave Peterson" wrote in message ... 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. Big Rick wrote: 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. -- Your help is and always has been very much appreciated. Thanking you in anticipation. --- --- --- Big Rick -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formatting of date | cebubum | General Discussion | 4 | June 28th, 2005 03:27 AM |
Date Range and Average F/X Rate | David | General Discussion | 0 | June 23rd, 2005 02:26 PM |
Problem formatting date values in chart... | Private Person | Charts and Charting | 7 | October 14th, 2004 05:00 AM |
QDE (Quick Date Entry) | Norman Harker | Worksheet Functions | 37 | September 5th, 2004 01:24 AM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |