Hi,
Is it possible to set the date format in a cell to 'Monday 18th March 2013'? If so I would then like to be able to enter the date on a Monday and have it automatically inserted on separate sheet for Tuesday to Friday?
Many thanks
Hi,
Is it possible to set the date format in a cell to 'Monday 18th March 2013'? If so I would then like to be able to enter the date on a Monday and have it automatically inserted on separate sheet for Tuesday to Friday?
Many thanks
You cannot apply special formatting to PARTS of a text string in a cell where that string is being displayed as a result of a formula. Only cells where you manually enter text strings can you apply partial-string formatting.
In short, if there's a formula, only whole-cell formatting can be used.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Many thanks, that's a shame :-(
In Excel 2007 and later you can use conditional formatting to display the ordinal date but I don't think you can format for super/subscript.
The date would be displayed as Monday 18th March 2013.
Is that acceptable?
EDIT: I've been meaning to post a thread in the Tips/Tutorial forum on this subject!
Last edited by Tony Valko; 03-13-2013 at 05:19 PM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
It would be better than nothing. I was just wondering if the format for the whole cell has to be the same would it be possible the have the date stretch across three cells the first with 'Monday 18' right justified the second with 'th' left justified and the third with March 2013 left justified? Bit of a palaver I know but it is on a sheet that the learning disabled pupils will see everyday and I really want them to see the example of how they should write the date properly?
See attached file. Put a date into the yellow cell (or =TODAY()) to see how it changes.
Hope this helps.
Pete
Here's a shorter ordinals formula:
=MID("stndrdthstndrdthst",MATCH(DAY(A4),{1,2,3,4,21,22,23,24,31})*2-1,2)
Even shorter...
Create these defined name...
Name: Ordinals
Refers to: ="stndrdthstndrdthst"
Name: Days
refers to: ={1,2,3,4,21,22,23,24,31}
Then:
=MID(Ordinals,MATCH(DAY(A4),Days)*2-1,2)
These only work for dates with day numbers from 1 to 31.
Last edited by Tony Valko; 03-14-2013 at 04:37 PM.
You can use the unicode characters in superscript
ᵃᵇᶜᵈᵉᶠᵍʰⁱʲᵏˡᵐⁿᵒᵖʳˢᵗᵘᵛʷˣʸᶻ
=MID("ˢᵗⁿᵈʳᵈᵗʰˢᵗⁿᵈʳᵈᵗʰˢᵗ",MATCH(DAY(A4),{1,2,3,4,21,22,23,24,31})*2-1,2)
And if you use math to extract the day it's even shorter
=MID("ᵗʰˢᵗⁿᵈʳᵈᵗʰ",MIN(MOD(MOD(DAY(A4),30),20),4)*2+1,2)
Sorry for reviving the thread, but I found it in my googling
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks