Format date within formula in VBA

[snb]

No variables make 'Option explicit' the more redundant.
It serves no purpose to repeat your opinions.
Not bothering about variable types enhances the learning curve of anybody.
The amount of readers is hopefully bigger than the TS and hopefully from more countries than only the US.
To add to everybody's leaning curve: preferably use Excel's builtin options and avoid clumsy coding:

Sub M_snb()
   With Sheet1.Cells(2, 16)
       .Parent.ListObjects.Add 1, .CurrentRegion
       .Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
    End With
End Sub

PS. It's no use commenting the suggestions put here by other helpers.
Please restrict yourself to your own suggestions.
I don't consider this forum as a debating forum.