Add quarters to date
To add quarters to a date you can apply the Excel EDATE or DATE function
Example: Add quarters to date
|
=EDATE(B5,C5*D5)
|
This formula adds five (5) quarters to the date specified in cell (B5) by using the EDATE function and multiply the number of months in a quarter and the number of quarters you want to add to the date. This formula links to specific cells in the worksheet for the formula parameters, however, you can also enter the number of quarters you want to add and the date that you want to add the quarters to directly into the formula (e.g. =EDATE("15/03/2017",3*5)).
|
|
This formula adds five (5) quarters to the date specified in cell (B5), by multiplying the number of months in a quarter and the number of quarters you want to add to the date, using the Excel DATE function. This formula links to specific cells in the worksheet for the formula parameters, however, you can also enter the number of quarters you want to add and the date that you want to add the quarters to directly into the formula (e.g. =DATE(YEAR("15/03/2017"),MONTH("15/03/2017")+3*5,DAY("15/03/2017"))).
|
Sub Add_quarters_to_date()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
Set squarters = ws.Range("D5")
Set sdate = ws.Range("B5")
Set squarters = ws.Range("D5")
Set sdate = ws.Range("B5")
'add the specified number of quarters to the date
ws.Range("G4") = DateAdd("q", squarters, sdate)
ws.Range("G4") = DateAdd("q", squarters, sdate)
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Quarters to add: This example references to cell ("D5") to source the number of quarters to add onto the date. Therefore, if using the same VBA code cell ("D5"), in the Analysis worksheet, must be populated with the value that represents the number of quarters you want to add to the date. You can also enter the number of quarters you want to add directly into the VBA code by replacing the cell reference (ws.Range("D5")) with the number of quarters.
Date: This example references to cell ("B5") to source the date that you want to add the quarters onto. Therefore, if using the same VBA code cell ("B5"), in the Analysis worksheet, must be populated with the date that you want to add the quarters onto. You can also enter the date directly into the VBA code by replacing the cell reference (ws.Range("B5")) with the date inside the double quotation marks (e.g. "15/03/2017").
Worksheet Name: Have a worksheet named Analysis.
Quarters to add: This example references to cell ("D5") to source the number of quarters to add onto the date. Therefore, if using the same VBA code cell ("D5"), in the Analysis worksheet, must be populated with the value that represents the number of quarters you want to add to the date. You can also enter the number of quarters you want to add directly into the VBA code by replacing the cell reference (ws.Range("D5")) with the number of quarters.
Date: This example references to cell ("B5") to source the date that you want to add the quarters onto. Therefore, if using the same VBA code cell ("B5"), in the Analysis worksheet, must be populated with the date that you want to add the quarters onto. You can also enter the date directly into the VBA code by replacing the cell reference (ws.Range("B5")) with the date inside the double quotation marks (e.g. "15/03/2017").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("G4") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Quarters to add: Select the number of quarters that you want to add to the date by changing the value in cell ("B5") in the Analysis worksheet.
Date: Select the date that you want to add the quarters onto by changing the date in cell ("B5") in the Analysis worksheet.
EXPLANATION
To add quarters to a date you can apply the Excel EDATE or DATE function. In this tutorial we explain how this can be achieved by using Excel and VBA.
To add quarters to a date you can apply the Excel EDATE or DATE function. In this tutorial we explain how this can be achieved by using Excel and VBA.
FORMULAS
=EDATE(date,number_of_months_in_quarter*number_of_quarters)
=DATE(YEAR(date),MONTH(date)+number_of_months_in_quarter*number_of_quarters,DAY(date))
=EDATE(date,number_of_months_in_quarter*number_of_quarters)
=DATE(YEAR(date),MONTH(date)+number_of_months_in_quarter*number_of_quarters,DAY(date))
ARGUMENTS
date: The date that you want to add the quarters onto.
number_of_months_in_quarter: Number of months in a quarters, which is three (3).
number_of_quarters: Number of quarters to add to the date.