Upper Case Dates

[zoom38]

I have a date in S1 and AA1 that I wish the month to be upper case. Formatting the cell will only allow for the month to be lower case, even trying to customize with capital MMMM it remains lower case. I'm sure this isn't a tough one to work on but I can't figure it out. I even tried using Upper(s1) but that reverted the date to a serial number. Any ideas?

Thanks
Gary

[austenr]

Somethng like this will return the current date with the month in upper case.

=UPPER(TEXT(TODAY(),"ddmmmyy"))

[parttime_guy]

Hi Gary,

I have attached the process of how to go about the date UPPER issue, plz have a look and let me have your views.

Thx-n-BR

[Ken Puls]

An intersting approach to use vlookup, parttime_guy, but personally, I prefer Austen's formula. Less to worry about with hiding sheets and such.

Just my 2 cents...

[parttime_guy]

I agree kplus, but does'nt the "=UPPER(TEXT(TODAY(),"ddmmmyy"))" function work only for the current date - it also has the date and year which is not required. I thought Gary problem was that he had a column (S1) full of dates and he wanted to have only the month(UPPER) of for that column to appear in the column (AA1).

Please let me have your views.

Thx-n-BR

[Ken Puls]

Interpretations...

I interpreted his need as Austen did... but we could both be wrong too though.

To the actual formula... I read that when Gary applies the date format it comes out as 23-Jul-05 for example, and he'd want something more like 23-JUL-05. Now I understand that Austen's example would yield 23JUL05, I only put in the -'s to break it up a bit.

As for the Today() part, you're correct, however we could easily sub in a range for that:
=Upper(Text(A1,"ddmmmyy"))

Or change the format slightly:
=Upper(Text(A1,"dd-mmm-yy")) or = Upper(Text(A1,"dd/mm/yyyy"))

It's flexible is all I'm saying.

[XLGibbs]

Okay, okay, okay...you are both right!

LOL.

As always the case with formatting dates in excel, the options are only as limited as your imagination on the topic. Since the OP has not yet responded ......safe to say, the issue is likely resolved....

[Zack Barresse]

But waaaaaaaiiiiittt a minute... here comes yet another solution .......

okay, so I'm just joking. (But we could use a UDF .... .. really.)

[parttime_guy]

Yo! Guz - But............ I agree that kplus approach is a faster and more easier way.

[Bob Phillips]

Doesn't he just want

=UPPER(TEXT(A1,"mmmm"))

[zoom38]

My appologies for getting back so late but I had a death in the family. Anyway thanks for the input. I like austenr's formula which I modifiedto "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 thatreference S1. When I use austenr's formula it messes up the cells thatreference it and I cannot seem to fix them. Cell D3 gives the Monthportion 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 myneeds. 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. ThanksGary