Return next month based on a date

This tutorial shows how to return the next month based on a specific date through the use of Excel formulas or VBA

Example: Return next month based on a date

Return next month based on a date

METHOD 1. Return next month based on a date

EXCEL

=TEXT(EDATE(B5,1),"mmmm")
This formula uses a combination of Excel TEXT and EDATE functions to calculate the next month based on a specific date. The formula uses the EDATE function with the months criteria of 1, therefore returning a date with an addition of one month. In this example it will return the 10/06/2017. Then using the TEXT function with the "mmmm" criteria the formula returns the month from the date, which in this example in June.

METHOD 2. Return previous month based on a date

EXCEL

=TEXT(EOMONTH(B5,1),"mmmm")
This formula uses a combination of the Excel TEXT and EOMONTH functions to calculate the next month based on a specific date. The formula uses the EOMONTH function with the specific date and the months criteria as 1 to return the last date of the next month from the selected date, which in this case would be 30/06/2017. Then using the TEXT function with the "mmmm" criteria the formula returns the month from the date, which in this example in June.

METHOD 1. Return next month based on a date using VBA

VBA

Sub Next_Month_based_on_a_Date()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the next month based on a specific date
ws.Range("D5") = Format(DateAdd("m", 1, ws.Range("B5")), "mmmm")

End Sub

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Date: Select the date from which to return the next month by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet where you want to return the next month based on a specific date 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 about the formulas used to calculate next month based on a date

EXPLANATION

EXPLANATION

This tutorial shows and explains how to calculate the next month based on a specific date using Excel formulas or VBA.
This tutorial provides two Excel methods that can be applied to return the next month based on a specific date. The first method uses a combination of the TEXT and EDATE functions whilst the second method uses the TEXT and EOMONTH 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 a specific date.
FORMULA (first method)
=TEXT(EDATE(date,1),"mmmm")
FORMULA (second method)
=TEXT(EOMONTH(date,1),"mmmm")
ARGUMENTS
date: The date from which to return the next month.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to return the next month based on the current month using Excel and VBA methods
How to return the previous month based on the current month using Excel and VBA methods
How to return the previous month based on a specific date using Excel and VBA methods
How to calculate the difference in days between two dates using Excel and VBA methods
How to calculate the difference in years between two dates using Excel and VBA methods

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel TEXT function returns a numeric value as text, in a specified format
The Excel EDATE function returns a date after the addition or subtraction of a specified number of months from a selected date