• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Format Issue Combining Text and Date

H

hardeep.kanwar

New Member
Hi All

i have formula "="Updated Joyride Program Summary Till Dated -"&TEXT(TODAY()-1,"DD-MMM-YYYY")&" "&"Cumulative"

and the result is "
Updated Joyride Program Summary Till Dated -25-Jul-2017 Cumulative
"

What i need to Add "th" or "rd" after the Day

For Example
23rd or 25th or 22nd
Updated Joyride Program Summary Till Dated -25th -Jul-2017 Cumulative
Updated Joyride Program Summary Till Dated -23rd -Jul-2017 Cumulative
Updated Joyride Program Summary Till Dated -22nd -Jul-2017 Cumulative


Thanks in Advance
 
Hi ,

One way is to add the following to your existing formula :

MID("stndrdththththththththththththththththstndrdthththththththst",DAY(TODAY()-1)*2-1,2)

Narayan
 
Perhaps

="Updated Joyride Program Summary Till Dated -"&DAY(TEXT(TODAY()-1,"DD-MMM-YYYY"))&CHOOSE(AND(DAY(TEXT(TODAY()-1,"DD-MMM-YYYY"))<>{11,12,13})*MIN(4,MOD(DAY(TEXT(TODAY()-1,"DD-MMM-YYYY")),10))+1,"th","st","nd","rd","th")&" "&"Cumulative"
 
Try.....

="Updated Joyride Program Summary Till Dated - "&DAY(TODAY()-1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(TODAY()-1))*(MOD(DAY(TODAY()-1)-11,100)>2)+1),2)&TEXT(TODAY()-1,"-mmm-yyyy")&" Cumulative"

Regards
Bosco
 
azumi said:
Perhaps

="Updated Joyride Program Summary Till Dated -"&DAY(TEXT(TODAY()-1,"DD-MMM-YYYY"))&CHOOSE(AND(DAY(TEXT(TODAY()-1,"DD-MMM-YYYY"))<>{11,12,13})*MIN(4,MOD(DAY(TEXT(TODAY()-1,"DD-MMM-YYYY")),10))+1,"th","st","nd","rd","th")&" "&"Cumulative"
Click to expand...


Thanks for the reply but its not working m getting below result after Change the Value to DAY(TEXT(TODAY()-3


Updated Joyride Program Summary Till Dated -23th Cumulative
 
bosco_yip said:
Try.....

="Updated Joyride Program Summary Till Dated - "&DAY(TODAY()-1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(TODAY()-1))*(MOD(DAY(TODAY()-1)-11,100)>2)+1),2)&TEXT(TODAY()-1,"-mmm-yyyy")&" Cumulative"

Regards
Bosco
Click to expand...
Thanks for the reply

Thanks for the reply but its not working m getting below result after Change the Value to DAY(TEXT(TODAY()-3

Updated Joyride Program Summary Till Dated - 23th-Jul-2017 Cumulative
 
You must log in or register to reply here.
Back
Top