Change the date

[rakupareek]

Hi
I have a question that
In Excel sheet row A1 future date inserted
In row B1 calculated no of days from today by formula
I want automatic change in date i.e. A1 when date less than today then in range A1 date to be changed next month same date
Please help to generate VBA code for this automatic changes

 

[SanjayGMusafir]

I want automatic change in date i.e. A1 when date less than today then in range A1 date to be changed next month same date

It can be done through a simple formula. All depends on when or at what difference you want it to change?

Just to clarify - Are you using Excel 2016 or have upgraded lately?

 

[mumps]

I would suggest one of two approaches.
1. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Whenever you activate the sheet, it will check the date in A1 and change it if necessary.

Private Sub Worksheet_Activate()
    If Range("A1") < Date And Month(Range("A1")) < 12 Then
        Range("A1") = DateSerial(Year(Range("A1")), Month(Range("A1")) + 1, Day(Range("A1")))
    Else
        Range("A1") = DateSerial(Year(Range("A1")) + 1, 1, Day(Range("A1")))
    End If
End Sub

2. Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Whenever you open the workbook, it will check the date in A1 and change it if necessary.

Private Sub Workbook_Open()
    With Sheets("Sheet1")
        If .Range("A1") < Date And Month(.Range("A1")) < 12 Then
            .Range("A1") = DateSerial(Year(.Range("A1")), Month(.Range("A1")) + 1, Day(.Range("A1")))
        Else
            .Range("A1") = DateSerial(Year(.Range("A1")) + 1, 1, Day(.Range("A1")))
        End If
    End With
End Sub

You could actually use both methods at the same time if you wish. Both macros take into account if the current month is December. If this is the case, the year is also changed.

 

[rakupareek]

Thanks Mumps
But this VBA not working

 

[mumps]

It worked properly in my tests using a dummy workbook. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[rakupareek]

Thanks you
Code works fine
Earlier I was make some mistake in code
Thak you very much Mumps

 

[mumps]

You are very welcome.