After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible to
refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?

Now using Excel 365 on my new Windows 10 PC, after 15 years with XP.
(Not happy at having to pay about £5/month for my occasional use, as I
could not get Excel 2000 from Office Pro 2000 working.)

--
Terry, East Grinstead, UK

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible to
refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?

if you insert the dates in column A then try this Worksheet_Change
event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count 1 Then Exit Sub

If IsDate(Target) Then
Select Case Day(Target)
Case 1, 11, 21, 31
Target.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
Target.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
Target.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
Target.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible
to refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?

if you insert the dates in column A then try this Worksheet_Change
event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count 1 Then Exit Sub

If IsDate(Target) Then
Select Case Day(Target)
Case 1, 11, 21, 31
Target.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
Target.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
Target.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
Target.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
End Sub


Regards
Claus B.

11 should use "th" (11th) not "st" (11st)...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Sun, 21 Aug 2016 13:24:04 -0400 schrieb GS:

11 should use "th" (11th) not "st" (11st)...

you are right. Thank you for improving my mistake.


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible to
refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?

if you insert the dates in column A then try this Worksheet_Change
event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count 1 Then Exit Sub

If IsDate(Target) Then
Select Case Day(Target)
Case 1, 11, 21, 31
Target.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
Target.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
Target.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
Target.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
End Sub


Regards
Claus B.

Thanks both.

In no-brainer terms, could you describe the steps I should take after
copying that code to the clipboard please? Is that just the same as a
macro?

--
Terry, East Grinstead, UK

Hi Terry,

Am Mon, 22 Aug 2016 17:19:06 +0100 schrieb Terry Pinnell:

In no-brainer terms, could you describe the steps I should take after
copying that code to the clipboard please? Is that just the same as a
macro?

press Alt+F11 = In Project Explorer double click on the expected sheet
and paste the code in the code window.


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Mon, 22 Aug 2016 17:19:06 +0100 schrieb Terry Pinnell:

In no-brainer terms, could you describe the steps I should take after
copying that code to the clipboard please? Is that just the same as a
macro?

press Alt+F11 = In Project Explorer double click on the expected sheet
and paste the code in the code window.


Regards
Claus B.

Thanks Claus. I chose 'This Workbook', pasted it, edited out the 11 in
Case 1, and returned to the worksheet. But how do I now apply it
please? Where do I access it in this complex Excel 365 UI? I found my
macros, but it's not in that list.

https://dl.dropboxusercontent.com/u/...el-Dates-3.jpg

I'm new to this version after 16 years with Excel 2000, the last
decade or so of which was very infrequent use, so thanks for your
patience ;-)

--
Terry, East Grinstead, UK

Hi Terry,

Am Mon, 22 Aug 2016 18:23:37 +0100 schrieb Terry Pinnell:

Thanks Claus. I chose 'This Workbook', pasted it, edited out the 11 in
Case 1, and returned to the worksheet. But how do I now apply it
please? Where do I access it in this complex Excel 365 UI? I found my
macros, but it's not in that list.

the code must be placed in the code module of the expected /worksheet/
not in ThisWorkbook.
Your dates will be formatted when you enter e.g.
23/7/16
Download from he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat".
There is the VBA solution as well the CF solution.


Regards
Claus B.
--
Windows10
Office 2016

I'm new to this version after 16 years with Excel 2000, the last
decade or so of which was very infrequent use, so thanks for your
patience

The VBE has remained the same since v2000. Beginning in v2010 VBA7 is
used; VBA6 persists for v2000 to v2007.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

Now using Excel 365 on my new Windows 10 PC, after 15 years with XP.
(Not happy at having to pay about £5/month for my occasional use, as I
could not get Excel 2000 from Office Pro 2000 working.)

with Excel 365 you can also use CF for formatting the dates.
Format all cells with
dddd d"th" MMMM YYYY
For the special dates then use CF.
E.g. for 1, 21 and 31:
New Rule = Formula to determine...:
=OR(DAY(D1)=1,DAY(D1)=21,DAY(D1)=31)
and format:
dddd d"st" MMMM YYYY
Add new rules for
2, 22 and 3, 23


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

Now using Excel 365 on my new Windows 10 PC, after 15 years with XP.
(Not happy at having to pay about £5/month for my occasional use, as I
could not get Excel 2000 from Office Pro 2000 working.)

with Excel 365 you can also use CF for formatting the dates.
Format all cells with
dddd d"th" MMMM YYYY
For the special dates then use CF.
E.g. for 1, 21 and 31:
New Rule = Formula to determine...:
=OR(DAY(D1)=1,DAY(D1)=21,DAY(D1)=31)
and format:
dddd d"st" MMMM YYYY
Add new rules for
2, 22 and 3, 23


Regards
Claus B.

For me, that seems the easiest method, at least for relatively few
entries.

But, without getting into complicated conditional formatting, can I
not now simply edit the few changes needed? Using F2 on cell C11 for
example displays '31/07/2016'. Must I delete those cells and re-enter
from scratch? Or, as I've done for now, capture them with an OCR tool,
paste and edit?

https://dl.dropboxusercontent.com/u/...el-Dates-2.jpg

I also tried to use one of the Paste Special options, unsuccessfully.

--
Terry, East Grinstead, UK

Hi Terry,

Am Mon, 22 Aug 2016 17:58:51 +0100 schrieb Terry Pinnell:

But, without getting into complicated conditional formatting, can I
not now simply edit the few changes needed? Using F2 on cell C11 for
example displays '31/07/2016'. Must I delete those cells and re-enter
from scratch? Or, as I've done for now, capture them with an OCR tool,
paste and edit?

https://dl.dropboxusercontent.com/u/...el-Dates-2.jpg

it looks like you have all dates formatted with
dddd d"th" MMMM YYYY
That is correct. Select your dates = CF = New Rule = Formula to
determine...
Insert the formula and format as expected.
Download he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat" and have a look at the custom numberformat and
the conditional formating.


Regards
Claus B.
--
Windows10
Office 2016

Hi again,

Am Mon, 22 Aug 2016 19:16:59 +0200 schrieb Claus Busch:

it looks like you have all dates formatted with
dddd d"th" MMMM YYYY
That is correct. Select your dates = CF = New Rule = Formula to
determine...
Insert the formula and format as expected.
Download he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat" and have a look at the custom numberformat and
the conditional formating.

I hope the formats will be translated when you open the file.
if not
tttt t"st" MMMM JJJJ means
dddd d"st" MMMM YYYY


Regards
Claus B.
--
Windows10
Office 2016