Return next month based on current month
This tutorial shows how to return the next month based on the current month through the use of Excel formulas or VBA
Example: Return next month based on current month
|
This formula uses a combination of Excel TEXT, EDATE and TODAY functions to calculate the next month based on the current month. The formula uses the EDATE and TODAY function, combined with a month criteria of 1 to return the same date next month, which in this example is 23/02/2019. Then using the TEXT function with the "mmmm" criteria the formula returns the month from the date, which in this example in February.
|
METHOD 2. Return next month based on current month
EXCEL
|
This formula uses a combination of Excel TEXT, EOMONTH and TODAY functions to calculate the next month based on the current month. The formula uses the EOMONTH and TODAY functions, with the months criteria as 1 to return the last date of the next month, which in this case would be 28/02/2019. Then using the TEXT function with the "mmmm" criteria the formula returns the month from the date, which in this example in February.
|
Sub Next_Month_based_on_Current_Month()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the next month based on the current month
ws.Range("C4") = Format(DateAdd("m", 1, Date), "mmmm")
ws.Range("C4") = Format(DateAdd("m", 1, Date), "mmmm")
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C4") in the VBA code.
Worksheet Selection: Select the worksheet where you want to return the next month based on the current month by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("C4") in the VBA code.
Worksheet Selection: Select the worksheet where you want to return the next month based on the current month by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows and explains how to calculate the next month based on the current month using Excel formulas or VBA.
This tutorial provides two Excel methods that can be applied to return the next month based on the current month. The first method uses a combination of the TEXT, EDATE and DAY functions whilst the second method uses the TEXT, EOMONTH and TODAY functions. The first method calculates the same date next month and then using the TEXT function with the "mmmm" criteria to return the month. The second method calculates the last date of the next month and then using the TEXT function with the "mmmm" criteria to return the month.
This tutorial provides one VBA method that can be applied to return the next month based on the current month.
FORMULA (first method)
=TEXT(EDATE(TODAY(),1),"mmmm")
=TEXT(EDATE(TODAY(),1),"mmmm")
FORMULA (second method)
=TEXT(EOMONTH(TODAY(),1),"mmmm")
=TEXT(EOMONTH(TODAY(),1),"mmmm")
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return previous month based on current month | How to return the previous month based on the current month using Excel and VBA methods | |
| Difference in months between two dates | How to calculate the difference in months between two dates using Excel and VBA methods | |
| Difference in days between two dates | How to calculate the difference in days between two dates using Excel and VBA methods | |
| Difference in years between two dates | How to calculate the difference in years between two dates using Excel and VBA methods | |
| Convert date to month name | How to convert a date into a month name using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| TEXT Function | The Excel TEXT function returns a numeric value as text, in a specified format | |
| TODAY Function | The Excel TODAY function returns the current date | |
| EDATE Function | The Excel EDATE function returns a date after the addition or subtraction of a specified number of months from a selected date |