Swapping dates and months

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Swapping dates and months

Post by shreeram.maroo »

Hi,

I have a range wherein I have mentioned the date in dd/mm/yyyy format.. Is there any way, I can swap the month and dates for the selected cells... I don't need to change the format to mm/dd/yyyy but i need to swap the date and month for selected cells.

For instance the date is 02/11/1995 - i want to make it 11/02/1995 and the date format will be dd/mm/yyyy in both cases

This will be extremely helpful. Thanks in advance

Regards
Shreeram

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Swapping dates and months

Post by HansV »

Here is a macro. Select the range before running it.
The macro will skip cells that don't contain a date, and also skip cells with a date whose day number is larger than 12.

Code: Select all

Sub SwapDayMonth()
    Dim c As Range
    Dim d As Date
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each c In Selection
        If IsDate(c.Value) Then
            d = c.Value
            If Day(d) <= 12 Then
                c.Value = DateSerial(Year(d), Day(d), Month(d))
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Swapping dates and months

Post by shreeram.maroo »

Thanks a ton Hans, this is really useful.

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Swapping dates and months

Post by adeel1 »

Hi sir Hans
what is purpose of this line :scratch:

d = c.Value

although value of both is remain same, please
Adeel

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Swapping dates and months

Post by HansV »

d is the value of a cell, for example 01/08/2021. The line

c.Value = DateSerial(Year(d), Day(d), Month(d))

switches the day and month, so that the value becomes 08/01/2021
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Swapping dates and months

Post by adeel1 »

thnx, i can understand what code is doing

but I want to know advantage of this line as if i change all d with c code still works.
then what is advantage is assigning c value to d please

Code: Select all

'd = c.Value
            If Day(c) <= 12 Then
                c.Value = DateSerial(Year(c), Day(c), Month(c))
Adeel

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Swapping dates and months

Post by HansV »

That will work too, but I prefer to refer explicitly to the Value property of a cell: c.Value, instead of implicitly: c.
In this situation, it doesn't make any difference, but in others, it can lead to errors in the code.
Here is an example:

Code: Select all

    Dim col As New Collection
    Dim c As Range
    Set c = ActiveCell
    col.Add Item:=c, Key:="First"
In the above code, we add the range object c to the collection col.
So we could later do something like this:

Code: Select all

    col("First").Interior.Color = vbRed
If we use

Code: Select all

    col.Add Item:=c.Value, Key:="First"
instead, we add the value of c to the collection col. col("First") will return the value of the cell, not the cell object.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Swapping dates and months

Post by adeel1 »

thnx sir, for explanation.

Adeel