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

Hi,

Am Tue, 06 Feb 2018 16:19:58 +0000 schrieb Terry Pinnell:

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?

a formula can't change a format. But you could try the formulas in the
conditional formatting.

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

I uploaded the workbook again:
https://1drv.ms/x/s!AqMiGBK2qniTgcFwk6ClS7kz-lZnvQ



Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi,

Am Tue, 06 Feb 2018 16:19:58 +0000 schrieb Terry Pinnell:

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?

a formula can't change a format. But you could try the formulas in the
conditional formatting.

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

I uploaded the workbook again:
https://1drv.ms/x/s!AqMiGBK2qniTgcFwk6ClS7kz-lZnvQ



Regards
Claus B.

Thanks Claus.

Terry, East Grinstead, UK