# Excel Date Question

#### ronaldlu

##### New Member
How can I get a date (4/2/05) to show "2nd"?

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try this.

It should account for any day and put the appropriate ending.

Change all occurrences of A1 to match your date cell.

=IF(AND(RIGHT(DAY(A1),2)<>"11",RIGHT(DAY(A1),1)<>"0"),DAY(A1)&CHOOSE(RIGHT(DAY(A1),1),"st","nd","rd","th","th","th","th","th","th"),DAY(A1)&"th")

Another possibility:

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

NBVC said:
=IF(AND(RIGHT(DAY(A1),2)<>"11",RIGHT(DAY(A1),1)<>"0"),DAY(A1)&CHOOSE(RIGHT(DAY(A1),1),"st","nd","rd","th","th","th","th","th","th"),DAY(A1)&"th")

NBVC: This gives 12nd and 13rd

Thanks, I ended up writing a function

Function GetDayFormat(x)

Select Case CStr(Day(x))
Case "1": g = "1st"
Case "2": g = "2nd"
Case "3": g = "3rd"
Case "21": g = "21st"
Case "22": g = "22nd"
Case "23": g = "23rd"
Case "31": g = "31st"
Case Else: g = Day(x) & "th"

End Select
GetDayFormat = g

End Function

Oaktree said:
NBVC said:
=IF(AND(RIGHT(DAY(A1),2)<>"11",RIGHT(DAY(A1),1)<>"0"),DAY(A1)&CHOOSE(RIGHT(DAY(A1),1),"st","nd","rd","th","th","th","th","th","th"),DAY(A1)&"th")

NBVC: This gives 12nd and 13rd

Thanks Oaktree. I remembered to take into account the 11th but not the 12th and 13th.

This fixes that
Code:
``=IF(AND(RIGHT(DAY(A1),2)<>"11",RIGHT(DAY(A1),2)<>"12",RIGHT(DAY(A1),2)<>"13",RIGHT(DAY(A1),1)<>"0"),DAY(A1)&CHOOSE(RIGHT(DAY(A1),1),"st","nd","rd","th","th","th","th","th","th"),DAY(A1)&"th")``

Replies
3
Views
144
Replies
11
Views
493
Replies
6
Views
356
Replies
3
Views
134
Replies
3
Views
178

1,207,387
Messages
6,078,200
Members
446,321
Latest member
thecachingyeti

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back