Return previous month based on a date
This tutorial shows how to return the previous month based on a specific date through the use of Excel formulas or VBA
Example: Return previous month based on a date
|
This formula uses a combination of Excel TEXT and DAY functions to calculate the previous month based on a specific date. The formula uses the DAY function to return the number of days that have elapsed during the specific month and then removes it from the specific date. This will return the last date of the previous month. Then using the TEXT function with the "mmmm" criteria the formula returns the month from the date, which in this example in April.
|
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 previous 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 month prior to the selected date, which in this case would be 30/04/2017. Then using the TEXT function with the "mmmm" criteria the formula returns the month from the date, which in this example in April.
|
Sub Previous_Month_based_on_a_Date()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the previous month based on a specific date
ws.Range("D5") = Format(DateAdd("m", -1, ws.Range("B5")), "mmmm")
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 previous month by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet where you want to return the previous 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.
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 previous month by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet where you want to return the previous 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
This tutorial shows and explains how to calculate the previous month based on a specific date using Excel formulas or VBA.
This tutorial provides two Excel methods that can be applied to return the previous month based on a specific date. The first method uses a combination of the TEXT and DAY functions whilst the second method uses the TEXT and EOMONTH functions. Both of the formulas work in a similar manner, where they calculate the last date of the previous month, based on the selected date, 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 previous month based on a specific date.
FORMULA (first method)
=TEXT(B5-DAY(date),"mmmm")
=TEXT(B5-DAY(date),"mmmm")
FORMULA (second method)
=TEXT(EOMONTH(date,-1),"mmmm")
=TEXT(EOMONTH(date,-1),"mmmm")
ARGUMENTS
date: The date from which to return the previous month.
date: The date from which to return the previous month.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return next month based on current month | How to return the next month based on the current month using Excel and VBA methods | |
| 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 |
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 | |
| DAY Function | The Excel DAY function returns the day from a specified date |