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
Swapping dates and months
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Swapping dates and months
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.
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
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Swapping dates and months
Thanks a ton Hans, this is really useful.
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Swapping dates and months
Hi sir Hans
what is purpose of this line
d = c.Value
although value of both is remain same, please
Adeel
what is purpose of this line
d = c.Value
although value of both is remain same, please
Adeel
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Swapping dates and months
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
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
Hans
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Swapping dates and months
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
Adeel
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))
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Swapping dates and months
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:
In the above code, we add the range object c to the collection col.
So we could later do something like this:
If we use
instead, we add the value of c to the collection col. col("First") will return the value of the cell, not the cell object.
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"
So we could later do something like this:
Code: Select all
col("First").Interior.Color = vbRed
Code: Select all
col.Add Item:=c.Value, Key:="First"
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Swapping dates and months
thnx sir, for explanation.
Adeel
Adeel