add st to 1 day of month 1

[DBAFrog]

I have a report that requires the Day of the Month to be formatted with the ST as part of it. i.e., 1st, 2nd, 3rd, etc.

Is there a format in the Formula builder to add the st, nd , rd to the date? If so what is the sytnax.

If not, has anyone built an if..else stmt to handle the addition of the st, nd to the day? If so, will you share it.

thanks,

 

[vidru]

Use a custom date format, and enter this formula for second_separator:

if datepart("d",CurrentFieldValue ) in [1,21,31] then "st, "
else if datepart("d",CurrentFieldValue) in [2,22] then "nd, "
else if datepart("d",CurrentFieldValue) in [3,23] then "rd, "
else "th, "

This works if you're actually dealing with a date. If it's just a number, then you should be able to adopt the above formula easily.

-dave

 

[DBAFrog]

Thanks, works great !!

 

[Rachel30]

Guys Thanks for you help. It did work. Is there anyway I could have the text of 1st 2nd 3rd 4th in small letters and at the top of the line. It's called superscript in word.