PDA

View Full Version : Upper Case Dates



zoom38
01-24-2006, 07:51 PM
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
01-24-2006, 09:45 PM
Somethng like this will return the current date with the month in upper case.

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

parttime_guy
01-28-2006, 07:57 PM
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 :thumb

Ken Puls
01-28-2006, 08:47 PM
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... :dunno

parttime_guy
01-29-2006, 07:34 PM
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
01-29-2006, 08:59 PM
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
01-30-2006, 06:49 AM
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
01-30-2006, 09:43 AM
But waaaaaaaiiiiittt a minute... here comes yet another solution .......

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

parttime_guy
01-30-2006, 07:04 PM
Yo! Guz - But............ I agree that kplus approach is a faster and more easier way.:bow: :thumb

Bob Phillips
01-31-2006, 01:11 AM
Doesn't he just want

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

zoom38
01-31-2006, 07:25 AM
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