Hi,
Try with Ucase
...
... = sDate & " " & UCase(sDay)

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"achidsey" wrote:

Excel Experts,

My code enters in A2 the date and day of the week for the last trade date
(business date) in the format:
10/19/05 Wednesday

However, I'd like the day of the week to be all Caps, so that the code enters:
10/19/05 WEDNESDAY

How would I change the code below to enter the day of the week in all caps.

Thanks in advance, Alan


Sub NewDate()
Dim sDate As String
Dim sDay As String

'Updates date field with last trading day

sDate = Evaluate("=TODAY()-IF(WEEKDAY(TODAY())=2,3,1)")
sDate = Format(sDate, "mm/dd/yy")

sDay = Evaluate("=TODAY()-IF(WEEKDAY(TODAY())=2,3,1)")
sDay = Format(sDate, "dddd")

Range("A2").Select
Selection.Formula = sDate & " " & sDay

End Sub


--
achidsey