• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 38
  • Last Modified:

Change dates in 2 Cells (first and last day of month)

Hi,

I have a sheet where I am constantly changing dates (first and last day of a month). Looking for a way to do this with one click.

Attached is an example of what I am looking for. Whatever month is in the cells, just hit increment or decrement to get the first and last dates for the next (or previous) month.

Thanks in advance,

swjtx99
DateExample.xlsx
0
swjtx99
Asked:
swjtx99
  • 3
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try something like this.....

Sub IncrementMonth()
Range("F1").Value = WorksheetFunction.EDate(Range("F1").Value, 1)
Range("F2").Value = WorksheetFunction.EoMonth(Range("F2").Value, 1)
End Sub

Sub DecrementMonth()
Range("F1").Value = WorksheetFunction.EoMonth(Range("F1").Value, -2) + 1
Range("F2").Value = WorksheetFunction.EoMonth(Range("F2").Value, -1)
End Sub

Open in new window

DateExample.xlsm
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
For IncrementMonth, you can also use the following code....

Sub IncrementMonth()
Range("F1").Value = WorksheetFunction.EoMonth(Range("F1").Value, 0) + 1
Range("F2").Value = WorksheetFunction.EoMonth(Range("F2").Value, 1)
End Sub

Open in new window

0
 
swjtx99Author Commented:
Worked Great!

Thanks again Subodh!

swjtx99
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now