Excel VBA - Display Concatenated Dates as DD MMM YYYY

TropicalMagic

New Member
Joined
Jun 19, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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:

SCREENSHOT 1.png


Here is the result I would like to obtain:

SCREENSHOT 2.png


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!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,453
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
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
AB
1
201 Agu 2020 - 31 Jul 2021
301 Feb 2021 - 31 Jul 2021
Sheet1
 
Solution

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,453
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,143,909
Messages
5,721,450
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top