Date Formatting

[Highrock]

I need a formula that writes the current date exactly as below (Using today's date as an example)


Dinner - Thursday 4th January 2018





ANY HELP GREATLY APPRECIATED I AM GOING MENTAL HERE LADS

 

[Joe4]

Welcome to the Board!

Adding the "th" is the tricky part. You could get it all except for that like this:

="Dinner - " & TEXT(TODAY(),"dddd d mmmm yyyy")

but adding the "st", "rd", "th" makes the formula much more complex, and might require VBA, depending on the answer to the following question.

Note that if you use a native Excel formula, it will ALWAYS show the current date. So, if you open the file today, it will show today's date. But if you open the file tomorrow, it will show tomorrow's date.
Is that what you want, or do you want the current date to be "frozen in time"? If so, that will require VBA, if you want it done automatically.

 

[njimack]

=TEXT(TODAY(),"dddd ")&DAY(TODAY())&IF(OR(DAY(TODAY())={1,2,3,21,22,23,31}),CHOOSE(1*RIGHT(DAY(TODAY())),"st ","nd ","rd "),"th ")&TEXT(TODAY(),"mmmm yyyy")

 

[Special-K99]

Ignore this, my solution didn't work

 

[Rick Rothstein]

Here is another formula (little bit shorter and less function calls) that will also work...

=SUBSTITUTE(TEXT(NOW(),"dddd d| mmmm yyyy"),"|",MID("thstndrdth",MIN(9,2*RIGHT(DAY(NOW()))*(MOD(DAY(NOW())-11,100)>2)+1),2))

 

[Highrock]

Thanks so much chaps I was losing my f****** marbles over this B******* problem.

Wonderful help!

Cheers!