Uppercase Date While Keeping DATE Format

[meantis]

I've been attempting to format a cell while maintaining the DATE() format:

MONTHNAME DD, YYYY

using the following code:

Sub uppercase()
    With Sheets("Sheet1").Range("B2")
        .Value = Date
        .NumberFormat = UCase(Format(Date, "mmmm ")) & UCase(Format(Date, "dd")) & ", yyyy"
    End With
End Sub

What do we need to get the correct output?

Thanks for any help and direction.

 

[Ardus Petus]

Sub uppercase()
    Sheets("Sheet1").Range("B2").value = ucase(format(date,"mmmm dd, yyyy"))
End Sub

 

[Fluff]

AFAIK you cannot format the the cell to show upper case month name.
You can convert the date to text & do it that way, but you would not be able to use the date in any form of calculation

 

[meantis]

Perhaps you're correct, @Fluff. @Ardus Petus solution makes sense and doesn't generate any runtime errors. Though the desired result isn't achieved. ?‍

 

[Fluff]

If your happy with text values, how about

   With Sheets("Sheet1").Range("B2")
      .Value = Date
      .NumberFormat = "@"
      .Value = UCase(Format(.Value, "mmmm dd, yyyy"))
   End With