By selecting B6 and applying Claus's macro below I get the correct
result displayed (having merged a few cells).

https://www.dropbox.com/s/rhikeaorb7...als-3.jpg?dl=0

But how can I do that with a formula instead please?

Sub FormatDate()
' 2017, from Claus
' Apply to 07/01/2018 (anywhere) to get Sunday 7th January 2018
' Target is formatted dd/mm/yyyy so could enter 7/1/18

Dim rngC As Range

For Each rngC In Selection
If IsDate(rngC) Then
Select Case Day(rngC)
Case 1, 21, 31
rngC.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
rngC.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
rngC.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
rngC.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
rngC = rngC.Text
Next
End Sub

BTW, I can no longer reach the link Claus originally provided about this
macro:

https://onedrive.live.com/redir?resi...=folder%2cxlsm

I get "This item might not exist or is no longer available."

Terry, East Grinstead, UK