Return next working day 6 months back
This tutorial shows how to return the next work day (business day) six months back using an Excel formula or VBA
Example: Return next working day 6 months back
|
=WORKDAY(EDATE(B5,-6)-1,1,$F$5:$F$12)
|
This formula uses the Excel WORKDAY and EDATE functions, including the holidays, to calculate the next working day six months before the selected date.
|
Sub Next_working_day_6_months_back()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the next working day 6 months back
ws.Range("C5") = WorksheetFunction.WorkDay(WorksheetFunction.EDate(ws.Range("B5"), -6) - 1, 1, ws.Range("F5:F12"))
ws.Range("C5") = WorksheetFunction.WorkDay(WorksheetFunction.EDate(ws.Range("B5"), -6) - 1, 1, ws.Range("F5:F12"))
End Sub
EXPLANATION
This tutorial shows how to return the next work day (business day) 6 months back through the use of an Excel formula or VBA.
Both the Excel formula and VBA methods make use of the WORKDAY and EDATE functions to return the next working day six months back.
FORMULA
=WORKDAY(EDATE(date,-6)-1,1,holidays)
=WORKDAY(EDATE(date,-6)-1,1,holidays)
ARGUMENTS
date: The initial date from which to begin counting the number of working days.
holidays: A list of dates that specifies the holidays to take into consideration.
date: The initial date from which to begin counting the number of working days.
holidays: A list of dates that specifies the holidays to take into consideration.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Show current date | How to return the current date using Excel and VBA | |
| Return next working day | How to return the next work day (business day) using Excel and VBA | |
| Return next working day 6 months in the future | How to return the next work day (business day) six months in the future using Excel and VBA |