formatting date

[Dummy Excel]

Hi All,
i have setup an invoice date and i need to automate the payment due date (7 days after the invoice date)

Ive formatted the date as dd mmmm yyyy although how do i automatically add the "st" (for 1st), or "nd" (for 2nd) etc? Need it to be 7th May 2019

thanks
Sam

 

[AlanY]

have a look here

https://www.mrexcel.com/forum/excel-questions/285128-st-nd-rd-th-format-date.html

 

[Dummy Excel]

yes, thats what im after although i changed the formula to suit

=DAY(A10+7)&MID("thstndrdth",MIN(9,2*RIGHT(A10)*(MOD(A10-11,100)>2)+1),2)&TEXT(TODAY()," mmmm yyyy")

appreciate your help
Sam

 

[Dummy Excel]

actually on further testing im getting incorrect details...
if A10 = 26/04/19, im getting 3st May 2019

any ideas?

 

[Norie]

Sam

If you are trying to get the ordinal suffix for A10+7 then you need to use A10+7 in the part of the formula that gets the suffix.


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

Note, I've used a different formula to get the suffix