Excel Date Question

[ronaldlu]

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

 

[NBVC]

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")

 

[Andrew Poulsom]

Simplistically you could format the cell as d"nd", but that woun't cater for 1st, 3rd, 4th etc,

For a generic solution look here:

http://www.mrexcel.com/board2/viewtopic.php?t=96196

 

[Oaktree]

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"})

 

[Oaktree]

=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

 

[ronaldlu]

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

 

[NBVC]

=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

=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")