Excel 2007 Custom date

See if you can use something from this link:

http://www.excelforum.com/tips-and-t...-or-later.html

You could try formatting the date with a custom format of d"th day of" mmm/yyyy

I'd be willing to bet that they don't want something like:

23th day of July 2013

I found this via Google search:

Formula:

Please Login or Register  to view this content.

This is on this site: http://excel.tips.net/T002510_Adding..._to_Dates.html

This one uses less functions to get the ordinal day:

=DAY(A1)&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31},{"st","nd","rd","th","st","nd","rd","th","st"})&TEXT(A1," mmmm, yyyy")

However, I think they want to FORMAT the cell to display the date that way, not use a helper formula.

Using the technique I describe in the link in post #3 you can create ordinal date formats if you're using Excel 2007 or later. I don't have the time to work it out tonight. Maybe I'll look at it again tomorrow if we get some feedback from the OP.

Hi..
One more option..

Formula:

Please Login or Register  to view this content.

Specially for OP.. try this one..

Formula:

Please Login or Register  to view this content.

Originally Posted by Debraj Roy

Hi..
One more option..

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

That formula is already mentioned in the link in post #3.

Here's how to use conditional formatting to display an ordinal date in the format "d[ordinal] day of mmmm,yyyy" (5th day of July,2013) in Excel versions 2007 and later.

Create the custom formats...

Select the cell you want to format. Let's assume that's cell A1.

Right click>Format Cells>Number tab>Custom

In that little box under Type: enter the format code:

d"st day of "mmmm,yyyy

Click OK

Repeat that process for these format codes:

d"nd day of "mmmm,yyyy
d"rd day of "mmmm,yyyy
d"th day of "mmmm,yyyy

Now, apply the conditional formatting...

Since there is a maximum of 31 days in any month we only need to deal with the ordinal suffix for the numbers 1 to 31 (days of the month).

With cell A1 still selected...

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31)

Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code:

d"st day of "mmmm,yyyy

OK>OK

New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=OR(DAY(A1)=2,DAY(A1)=22)

Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code:

d"nd day of "mmmm,yyyy

OK>OK

New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=OR(DAY(A1)=3,DAY(A1)=23)

Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code:

d"rd day of "mmmm,yyyy

OK>OK

New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=OR(AND(DAY(A1)>=4,DAY(A1)<=20),AND(DAY(A1)>=24,DAY(A1)<=30))

Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code:

d"th day of "mmmm,yyyy

OK out

Try it out. Enter some date in cell A1 like 7/5/2013. Excel will display that date as 5th day of July,2013. The cell is still a numeric date value.

The cell can also contain a formula that returns a date, for example:

=TODAY()
=EDATE(NOW(),12)