Automation of Date

[Dharmendra_Bharwad]

There is date appearing as 02/28/2021 in cell B6 in the attached excel sheet. I want that when the "Previous Month" button is selected, the date in cell B6 should change to 01/31/2021. Similarly when button "Next Month" is selected, the date in cell B6 should change to 03/31/2021.

I don't how this can be achieved but it is of utmost important to achieve this. Any similar idea/s is/are also welcomed.

Thanks.

[#2]

@Dharmendra_Bharwad 

This can be done with a couple of macros. See the attached version - now a .xlsm workbook.

Make sure that you allow macros when you you open the workbook.

P.S. The value in cell B6 was a text value, I converted it to a 'real' date.

[#3]

@Hans Vogelaar Thanks a lot for the solution. yes, it works exactly as I wanted.

But it would be great if you can specify each step by step as I need to have multiple similar macros to be enabled in other workbooks too.

[#4 · Accepted solution]

@Dharmendra_Bharwad 

I did the following:

• I right-clicked the Previous Month button and selected Assign Macro... from the context menu.
• I entered PreviousMonth in the Macro name box (a macro name cannot contain spaces), then clicked New.
• This took me to the Visual Basic Editor, with the first and last lines already created. It looked like this:

Sub PrevMonth()

End Sub

• I typed the code into the line in between. It takes the value of cell B6, and used the EOMONTH function to store the last day of the previous month in B6:

Sub PrevMonth()
    Range("B6").Value = Application.EoMonth(Range("B6").Value, -1)
End Sub

• I switched back to Excel, then did basically the same for the Next Month button, but with a different line of code, of course.
• Finally, I pressed F12 from the workbook to display the Save As dialog.
• I selected Excel Macro-enabled Workbook (*.xlsm) from the Save as type dropdown, then clicked Save.

[#5]

@Hans Vogelaar 

I was wondering if you can help me to have the same result in Google Sheet then it would be double thumbs up to you.

[#6]

@Dharmendra_Bharwad 

I have very little experience with Google Sheets, sorry.