LinkBack URL
About LinkBacks
Hi,
I have cell values of 1,2,3 etc that are used with other formula on a worksheet. However I would like these cells to display the format of 1st, 2nd, 3rd, 4th etc without any month or year values. Is this possible.
Thank you
Hi,
I have cell values of 1,2,3 etc that are used with other formula on a worksheet. However I would like these cells to display the format of 1st, 2nd, 3rd, 4th etc without any month or year values. Is this possible.
Thank you
Last edited by jpruffle; 03-28-2009 at 10:49 AM.
A1: (a whole number)
This formula trans forms that value into an ordinal…eg 1st, 2nd, 11th, etc
Please Login or Register to view this content.
Is that something you can work with?
That is fantastic works like a dream.
Thank you
I guess I am not doing it right. I got it to work 1st, 2nd, 3rd, But the format I want is;Originally Posted by Ron Coderre
A1: (a whole number)
This formula trans forms that value into an ordinal…eg 1st, 2nd, 11th, etc
Please Login or Register to view this content.
Is that something you can work with?
Sunday, April 11th. How can I get 4/11/2010 into that format for a whole list of dates?
With A1 containing a date
Try this formula:
Please Login or Register to view this content.
Some sample A1, B1 values:
04/01/10 Thursday, April 1st
04/02/10 Friday, April 2nd
04/03/10 Saturday, April 3rd
04/04/10 Sunday, April 4th
04/05/10 Monday, April 5th
04/19/10 Monday, April 19th
04/20/10 Tuesday, April 20th
04/21/10 Wednesday, April 21st
04/22/10 Thursday, April 22nd
04/29/10 Thursday, April 29th
04/30/10 Friday, April 30th
05/01/10 Saturday, May 1st
Does that help?
I like to use LOOKUP, i.e.
=TEXT(A1,"dddd, mmmm d")&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})
Your less arcane approach got me to re-think mine.
I came up with this:
=TEXT(D1,"dddd, mmmm d")&MID("stndrdthstndrdthst",MATCH(DAY(D1),{1,2,3,4,21,22,23,24,31},1)*2-1,2)
That works great!!!! Another friend of mine came up with:
=TEXT(A1,"dddd, mmmm d")&MID("thstndrd",(MOD(DAY(A1),10)*AND(MOD(DAY(A1),10)<4,OR(DAY(A1)<11,DAY(A1)>20)))*2+1,2)
Jpruffle,
I had got this basic code somewhere in the forum...it does not belong to me..
Just thought of adding to it though..
RegardsPlease Login or Register to view this content.
E
Hallo
I have this code:
Caption = format(Date, "mmmm") & ", " & format(Date, "dd") & Mid("thstndrdth", (DatePart("d", Date) Mod 10) * 2 + 1, 2) & ", " & format(Date, "yyyy")
How get i the "thstndrdth" in Superscript?
Regards
Mark
A formula cannot affect the format of a cell, so you can't have part of it in superscript (without VBA).
Anyway, you shouldn't ask a question in someone else's thread - start your own (see Forum Rules at the top of the screen).
Hope this helps.
Pete
the simplest that i found is:
=text(today();"dd")&"th"&text(today();"mmm yyyy")
the simplest that i found is:
=text(today();"dd")&"th"&text(today();"mmm yyyy")
you can change the "red color value" of your choice
is there a way to use custom format to get the "thstndrdth"? I need to be able to calculate of the result but would prefer if the "rd" to auto fill based on a changing result. IE "When is the company profitable?" Answer:"3rd Quarter from IPO" where the cell contents are only a 3 so i can use array sumifs and the like off of that cell.
Which is why i prefer "Custom Cell Format" for those types of issues
coinbank Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
@ coinbank...
I can answer your question BUT you'll have to start your own thread as per forum rules.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks