GS wrote:

I have to leave my PC for a couple of hours now for Saturday chores. My
intention after returning is to try writing another VBA macro after the
import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it
with Macro Express Pro.

Terry, East Grinstead, UK

Did you try to set the format to show the short name of the month instead of
its numeric value? What I see in B6 and the format you've chosen makes sense
for OSs after XP; -it's interpreting day=8, month=12. In XP that would be the
other way around; month=8, day=12.

You cou make a macro to parse the left side of B5 and build the date format
from its parts:

Option Explicit

Sub FixDate()
Dim m, d, y, s
s = Range("B5").Value
y = Mid(s, 1, 4): m = Mid(s, 5, 2): d = Mid(s, 7, 2)
With Range("B6")
.NumberFormat = "mmm dd, yyyy": .Value = m & "/" & d & "/" & y
End With
End Sub

Note that I use the above format for reliable consistency with date cells so
there's no ambiguity for month/day. (IOW, eliminate the cause and you'll always
have clarity!) Edit that to your liking.

In financial transaction sheets where the calendar year is a fiscal year, I
omit the year in the TransactionDate field so "mmm-dd" is all that displays.

If you don't want macros in the project file, put this is PERSONAL.xls.

Hi Garry,

Your macro works fine here, many thanks. I've currently applied it to K6
in the DD/MM/YY format.

But meanwhile I'd implemented an approach using a formula.

With the date 12/08/17 in B6, the following formula I obtained elsewhere
a couple of weeks ago

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

clevely displayed it complete with ordinals, i.e:
Saturday 12th August 2017

So I've now edited that formula and it can be applied to B5 to give the
same result. But it was a VERY tedious and error-prone task. I was
rather amazed that it worked! Is there a neater way to do it please?

=TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"dddd")&"
"&DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))&IF(OR(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))={1,2,3,21,22,23,31}),CHOOSE(1*RIGH T(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2))),1),"st
","nd ","rd "),"th
")&TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"mmmm yyyy")

Finally (for now!) is there a general way to code a VBA macro to insert
that or any formula? Both seem to have have pros and cons so I'd value
the choice.

Terry, East Grinstead, UK