Solved: UPPER CASE DATES AGAIN

[zoom38]

My original post "Upper Case Dates" I liked austenr's formula which I modified to "MMMM dd, yyyy" which works for cell Z1 but not so well in cell S1. S1 is not TODAY's date it is user input and I have other cells that reference S1. When I use austenr's formula it messes up the cells that reference it and I cannot seem to fix them. Cell D3 gives the Month portion of the date in S1. Cell D4 gives the weekday portion of thedate in S1. Cell D5 gives the day portion of the date in S1. Then Ihave 27 days that reference D3, D4, and D5 which gives me a 28 dayschedule. Parttime_guy's VLOOKUP version is nice but doesn't fit my needs. Do I have to use vba just to format one cell(S1)? Take a look at the attached file to give you a better understanding of of what I am looking for. If I do need to use vba can someone help me out, I tried but couldnt get it and I don't think I was even close.
Thanks
Gary

[matthewspatrick]

Working on worksheet RVSD_Squad 1, I did the following:

In the module for that worksheet, I added this code:

[VBA] Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[s1]) Is Nothing Then
Me.[s1].Formula = "=""" & UCase(Format(Me.[s1], "mmmm d, yyyy")) & """"
End If

End Sub

[/VBA]

In Z1 I put the formula:
=UPPER(TEXT($S$1+27,"mmmm d, yyyy"))

In D3 & D4 I put the formula:
=DATEVALUE($S$1)

Everything else appeared to flow through quite nicely.

Patrick

[zoom38]

Patrick, I have to admit I don't know what is going on with your VBA code but it worked fine. The Datevalue worked great too. Thanks for the help.

Gary

[matthewspatrick]

Patrick, I have to admit I don't know what is going on with your VBA code but it worked fine.

No worries

Here is the code, demystified:

1. The sub is a special kind, called an 'event sub'. Whenever there is a change on that worksheet (the user enters/edits/deletes something from a cell, inserts a column, etc.), Excel calls the sub automatically (Target is the range that changed)
2. The Intersect function returns a range that is the common element of two or more ranges. Thus, the intersection of C:F and 17:18 would be C17:F18. If there is no intersection, the function returns Nothing. So, the If statement is testing to see if S1 is part of the range that changed. If it is, the next line executes
3. If S1 changed, then the sub reads the value stored there, and I used the Format function to force a text string of mmmm d, yyyy. I stuck that value back into the cell as a formula because, when I tried to put it back in just using Format, Excel in its zeal to be helpful kept converting it to a real date. I did not want a real date, because I do not think I could have gotten a NumberFormat to capitalize the month name.

Regards,

Patrick