marku24
asked on
Format Date in Excel
I have a Date in Excel of 5/2/2011, I would like this date to be appended to a text field that starts with "My Date is"........ That said, I would like to convert the date above and have it appear as "My Date is May 2nd". When I use MONTH() I get a 5 and not "May". Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
MONTH returns a month number, not a month name.
You could use a formula like this:
="My date is "&TEXT(A2,"mmmm d")
You could use a formula like this:
="My date is "&TEXT(A2,"mmmm d")
slow fingers :)
ASKER
works great, thanks
To display the ordinal of the day use this function:
="My Date is "&TEXT(A1,"MMM D")&CHOOSE(MIN(MOD(IF(AND( MOD(DAY(A1 ),100)>10, MOD(DAY(A1 ),100)<14) ,4,DAY(A1) )-1,10)+1, 4),"st","n d","rd","t h")
Kevin
="My Date is "&TEXT(A1,"MMM D")&CHOOSE(MIN(MOD(IF(AND(
Kevin
You can use a formula like this:
="my date is "&CHOOSE(MONTH(A1),"Jan"," Feb","Mar" ,"Apr","Ma y","Jun"," Jul","Aug" ,"Sep","Oc t","Nov"," Dec")&" " &DAY(A1) & "nd"
Take a look at the attached example.
jppinto
Book2.xlsx
="my date is "&CHOOSE(MONTH(A1),"Jan","
Take a look at the attached example.
jppinto
Book2.xlsx
="My Date is "&TEXT(A1,"MMM D")
Kevin