Subtract weeks from date
To subtract weeks from a date you can directly subtract the number of weeks from the formula, by multiplying by the number of days in a week by the number of weeks you want to subtract, or apply the Excel DATE function
Example: Subtract weeks from date
|
=B5-C5*D5
|
This formula subtracts five (5) weeks directly from the date specified in cell (B5), by multiplying the number of days in a week and the number of weeks you want to subtract from the date. This formula links to specific cells in the worksheet for the formula parameters, however, you can also enter the number of weeks you want to subtract and the date that you want to subtract the weeks directly from the formula (e.g. ="15/03/2017"-7*5).
|
|
This formula subtracts five (5) weeks from the date specified in cell (B5),by multiplying the number of days in a week and the number of weeks 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 weeks you want to subtract and the date that you want to subtract the weeks from directly into the formula (e.g. =DATE(YEAR("15/03/2017"),MONTH("15/03/2017"),DAY("15/03/2017")-7*5)).
|
Sub Subtract_weeks_from_date()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
Set sweeks = ws.Range("D5")
Set sdate = ws.Range("B5")
Set sweeks = ws.Range("D5")
Set sdate = ws.Range("B5")
'add the specified number of weeks to the date
ws.Range("G4") = DateAdd("ww", -sweeks, sdate)
ws.Range("G4") = DateAdd("ww", -sweeks, 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.
Weeks to subtract: This example references to cell ("D5") to source the number of weeks to subtract from 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 weeks you want to subtract from the date. You can also enter the number of weeks you want to subtract directly into the VBA code by replacing the cell reference (ws.Range("D5")) with the number of weeks.
Date: This example references to cell ("B5") to source the date that you want to subtract the weeks from. Therefore, if using the same VBA code cell ("B5"), in the Analysis worksheet, must be populated with the date that you want to subtract the weeks from. 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.
Weeks to subtract: This example references to cell ("D5") to source the number of weeks to subtract from 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 weeks you want to subtract from the date. You can also enter the number of weeks you want to subtract directly into the VBA code by replacing the cell reference (ws.Range("D5")) with the number of weeks.
Date: This example references to cell ("B5") to source the date that you want to subtract the weeks from. Therefore, if using the same VBA code cell ("B5"), in the Analysis worksheet, must be populated with the date that you want to subtract the weeks from. 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.
Weeks to subtract: Select the number of weeks that you want to subtract from the date by changing the value in cell ("B5") in the Analysis worksheet.
Date: Select the date that you want to subtract the weeks from by changing the date in cell ("B5") in the Analysis worksheet.
EXPLANATION
To subtract weeks from a date you can directly subtract the number of weeks from the formula, by multiplying by the number of days in a week by the number of weeks you want to subtract, or apply the Excel DATE function. In this tutorial we explain how this can be achieved by using Excel and VBA.
To subtract weeks from a date you can directly subtract the number of weeks from the formula, by multiplying by the number of days in a week by the number of weeks you want to subtract, or apply the Excel DATE function. In this tutorial we explain how this can be achieved by using Excel and VBA.
FORMULAS
=date - number_of_days_in_week * number_of_weeks
=DATE(YEAR(date),MONTH(date),DAY(date)-number_of_days_in_week*number_of_weeks)
=date - number_of_days_in_week * number_of_weeks
=DATE(YEAR(date),MONTH(date),DAY(date)-number_of_days_in_week*number_of_weeks)
ARGUMENTS
date: The date that you want to subtract the weeks onto.
number_of_days_in_week: Number of days in a week, which is seven (7).
number_of_weeks: Number of weeks to subtract to the date.