Excel VBA - Display Concatenated Dates as DD MMM YYYY

[TropicalMagic]

I would like to display concatenated dates in the format of DD MMM YYYY.

I have obtained 3 dates, last day 1 month ago, first day 6 months ago and first day 12 months ago, all from today's date.

Here is the code generating my first screenshot:

```
Dim myDate As Date
myDate = Date 'Returns today's date
MyWorkSheetName.Range("A1") = DateSerial(Year(myDate), Month(myDate), 0) 'Returns last day 1 month ago
MyWorkSheetName.Range("B1") = DateSerial(Year(myDate), Month(myDate) - 6, 1) 'Returns first day 6 months ago
MyWorkSheetName.Range("C1") = DateSerial(Year(myDate), Month(myDate) - 12, 1) 'Returns first day 12 months ago

MyWorkSheetName.Range("A3").NumberFormat = "DD MMM YYYY"
MyWorkSheetName.Range("B3").NumberFormat = "DD MMM YYYY"
MyWorkSheetName.Range("C3").NumberFormat = "DD MMM YYYY"
```

Here is my first screenshot:

Here is the result I would like to obtain:

I would like to display them as "01 Feb 2020 - 31 Jul 2021" in a single cell, but concatenating MyWorksheetName.Range("A1").NumberFormat = "DD MMM YYYY" with MyWorksheetName.Range("B1").NumberFormat = "DD MMM YYYY" did not generate the result.

Many thanks!

 

[Akuini]

Try:

Sub a1180181a()
Dim mydate As Date
Dim dt1 As Date, dt2 As Date, dt3 As Date
mydate = Date 'Returns today's date
dt1 = DateSerial(Year(mydate), Month(mydate), 0) 'Returns last day 1 month ago
dt2 = DateSerial(Year(mydate), Month(mydate) - 6, 1) 'Returns first day 6 months ago
dt3 = DateSerial(Year(mydate), Month(mydate) - 12, 1) 'Returns first day 12 months ago

Range("B2") = Format(dt3, "DD MMM YYYY") & " - " & Format(dt1, "DD MMM YYYY")
Range("B3") = Format(dt2, "DD MMM YYYY") & " - " & Format(dt1, "DD MMM YYYY")

End Sub

Book1
	A	B
1
2		01 Agu 2020 - 31 Jul 2021
3		01 Feb 2021 - 31 Jul 2021
Sheet1

 

[TropicalMagic]

Fantastic, thanks! It worked as I intended!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.