-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Formatting dates with st, nd, rd etc?
- Thread starter merlin777
- Start date
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
Perhaps this in the sheet's code module
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oneCell As Range
For Each oneCell In Target
With oneCell
If .Column = 3 Then
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm"
Case 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 24, 25, 26, 27, 28, 29, 30
.NumberFormat = "dddd d""th"" mmm"
End Select
End If
End If
End With
Next oneCell
End Sub
Upvote
0
thanks for this.
I went to sheet tab/view code and pasted your code.
I inserted 10 consecutive dates and formatted them e.g. 'Monday 01 January' but there didn't seem to be any effect from the code. am i doing something wrong?
I went to sheet tab/view code and pasted your code.
I inserted 10 consecutive dates and formatted them e.g. 'Monday 01 January' but there didn't seem to be any effect from the code. am i doing something wrong?
Upvote
0
ah, i see i'm wrong 1 it work son any cell but only when the cell is changed.
I just out the code in the where it is to end up and now its not working.
I tried tpying a date in an empty cell '1/1/2014' and there was no change on this or the existing date cells.
of the 5 column headings for 5 dates, mon, B7, gets it's contents from a formula:
=FIRSTDAYOFYEAR+(7*$C$2)-7
and the other 4 days are =b7+1,= c7+1 etc
am i doing something wrong?
I just out the code in the where it is to end up and now its not working.
I tried tpying a date in an empty cell '1/1/2014' and there was no change on this or the existing date cells.
of the 5 column headings for 5 dates, mon, B7, gets it's contents from a formula:
=FIRSTDAYOFYEAR+(7*$C$2)-7
and the other 4 days are =b7+1,= c7+1 etc
am i doing something wrong?
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
This should work
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oneCell As Range
Dim keyRange As Range
Set keyRange = Range("B7:F7")
On Error Resume Next
Set keyRange = Application.Union(keyRange, keyRange.Precedents)
On Error GoTo 0
If Not Application.Intersect(Target, keyRange) Is Nothing Then
For Each oneCell In Application.Intersect(Target, keyRange)
With oneCell
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm"
Case 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 24, 25, 26, 27, 28, 29, 30
.NumberFormat = "dddd d""th"" mmm"
End Select
End If
End With
Next oneCell
End If
End Sub
Upvote
0
I'm trying to understand what cusses the changes to apply.
I started with a new sheet/book
I entered a date by hand '10/8/2014' and it displayed as '10/8/2014'
I reformatted manually to 'Monday 10 August' and that's how it stayed
I cut and pasted it to another cell - no change
I copied and pasted it to another cell and the 'th' appeared
and now, it doesn't matter what I try I can't enter a date and have the format change automatically
...mystified
How should it work, mike? Ideally it need to work on a small range of cells which show data from another sheet using index/row/column and not from manual entry.
I started with a new sheet/book
I entered a date by hand '10/8/2014' and it displayed as '10/8/2014'
I reformatted manually to 'Monday 10 August' and that's how it stayed
I cut and pasted it to another cell - no change
I copied and pasted it to another cell and the 'th' appeared
and now, it doesn't matter what I try I can't enter a date and have the format change automatically
...mystified
How should it work, mike? Ideally it need to work on a small range of cells which show data from another sheet using index/row/column and not from manual entry.
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
When the user enters a value in a cell, that cell triggers a Change Event.
If there is another cell that contains a formula that refers to the first cell, that formula cell does not trigger a Change
The code in post #7 looks to see if B7:F7 or their precedents has changed. And I see where my logic was faulty.
Just to be clear:
The only cells where you want ordinal dates are B7:F7? Correct?
Some of those cells have user entered values some have formulas? Correct?
If there is another cell that contains a formula that refers to the first cell, that formula cell does not trigger a Change
The code in post #7 looks to see if B7:F7 or their precedents has changed. And I see where my logic was faulty.
Just to be clear:
The only cells where you want ordinal dates are B7:F7? Correct?
Some of those cells have user entered values some have formulas? Correct?
Upvote
0
Similar threads
