Difference in days between two dates
To calculate the difference in days between two dates you can apply the Excel DATEDIF function or directly subtract the start date from the end date
Example: Difference in days between two dates
|
=C5-B5
|
The formula subtracts the start date from the end date to return the number of days between the two dates. The start and end dates are captured in cells B5 and C5, respectively. You can also enter the dates directly into the formula but would need to apply the double quotation marks (e.g. ="20/10/2017"-"15/03/2017").
|
METHOD 2. Difference in days between two dates using DATEDIF function
EXCEL
|
=DATEDIF(B5,C5,D5)
|
The formula returns the number of days between the two selected dates. The start and end dates are captured in cells B5 and C5, respectively. Cell D5 captures the time unit, which in this case letter d represents days. If you prefer to enter formula arguments manually, you will need to use double quotation marks (e.g. =DATEDIF("15/03/2017","20/10/2017","d")).
|
Dim ws As Worksheet
Set date1 = ws.Range("B5")
Set date2 = ws.Range("C5")
ws.Range("G4") = date2 - date1
End Sub
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.
Worksheet Name: Have a worksheet named Analysis.
Start Date: If using the exact VBA code the start date needs to be captured in cell ("B5").
End Date: If using the exact VBA code the end date needs to be captured in cell ("C5").
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.
Start Date: Select the cell that captures the start date by changing the cell reference ("B5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
End Date: Select the cell that captures the end date by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 2. Difference in days between two dates using VBA with DATEDIF function
VBA
Dim ws As Worksheet
Set date1 = ws.Range("B5")
Set date2 = ws.Range("C5")
Set sday = ws.Range("D5")
ws.Range("G4") = DateDiff(sday, date1, date2)
End Sub
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.
Worksheet Name: Have a worksheet named Analysis.
Start Date: If using the exact VBA code the start date needs to be captured in cell ("B5").
End Date: If using the exact VBA code the end date needs to be captured in cell ("C5").
Time Unit: If using the exact VBA code the time unit needs to be captured in cell ("D5"), which will hold the letter d.
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.
Start Date: Select the cell that captures the start date by changing the cell reference ("B5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
End Date: Select the cell that captures the end date by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Time Unit: Select the cell that captures the time unit by changing the cell reference ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 3. Difference in days between two dates using VBA with a formula function
VBA
Dim ws As Worksheet
ws.Range("E5").Formula = "=DATEDIF(B5,C5,D5)"
End Sub
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.
Worksheet Name: Have a worksheet named Analysis.
Start Date: If using the exact VBA code the start date needs to be captured in cell ("B5").
End Date: If using the exact VBA code the end date needs to be captured in cell ("C5").
Time Unit: If using the exact VBA code the time unit needs to be captured in cell ("D5"), which will hold the letter d.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Start Date: Select the cell that captures the start date by changing the cell reference ("B5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
End Date: Select the cell that captures the end date by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Time Unit: Select the cell that captures the time unit by changing the cell reference ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Explanation about the formula used to calculate the difference in days between two dates
EXPLANATION
To calculate the difference in days between two dates you can apply both and Excel and VBA methods. Using an Excel method you can apply the Excel DATEDIF function or directly subtract the start date from the end date. If using VBA, we either use a DateDiff function, directly subtract the start date from the end date or apply the DATEDIF formula.
=end_date-start_date
=DATEDIF(start_date,end_date,time_unit)
ARGUMENTS
start_date: The starting date from which you want to begin counting days.
end_date: The end date up to which to count the number of days.
time_unit: The time unit representation. In this tutorial given we are calculating the number of days between two dates we would apply the letter d.