-
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.
Custom Date formats - Capitalized
- Thread starter sebjac
- Start date
Richard Schollar
MrExcel MVP
- Joined
- Apr 19, 2005
- Messages
- 23,707
Hi
You'll need to use a formula I'm afraid something like:
=UPPER(TEXT(Date,"dddd dd mmm yyyy"))
where Date is your date cell, for example.
You'll need to use a formula I'm afraid something like:
=UPPER(TEXT(Date,"dddd dd mmm yyyy"))
where Date is your date cell, for example.
Upvote
0
Hi Seb,
Welcome to the board!
This isn't possible to do using custom formatting. You can either use data validation to only allow uppercase entry (but if someone is using numbers that are being "converted" into days and months this won't work).
The alternative is to use VBA. The following code should work. Bear in mind however, that is changes the Excel date serial to a text string. This means you won't be able to use it in calculations.
Welcome to the board!
This isn't possible to do using custom formatting. You can either use data validation to only allow uppercase entry (but if someone is using numbers that are being "converted" into days and months this won't work).
The alternative is to use VBA. The following code should work. Bear in mind however, that is changes the Excel date serial to a text string. This means you won't be able to use it in calculations.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Format(Target.Value, "dddd dd mmm yyyy"), vbUpperCase)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
Upvote
0
