Here’s a question from Andrea:
Quick question here, new Google Sheets user: how do i subtract dates in Google Sheets – i need to calculate the date difference between two date values: today and two project milestones in the future. I need to calculate the result in both calendar dates and working days – Any idea on how to proceed?
Difference between today and other date value in Sheets
Step 1: Prepare your Google Spreadsheet
- Input data into the spreadsheet by manually typing it or import it from an external source.
- Use the =TODAY() function to input the current date in your worksheet.
- In our case, i have added today’s date in cell B8. We will use an absolute reference to this cell in our calculations in Steps 2 & 3.
Step 2: Calculate the difference between two date values
Option1:
To find the number of days from a given date to today use the DAYS() function. The formula syntax is the following:
=DAYS(date_value,current_date)
A different method to accomplish the same result is using the DATEDIF function. The syntax is:
= DATEDIF(current_date,date_value,"D")
Note: In order to calculate the time in months,you need to change a bit the time unit value in the DATEDIF formula: =DATEDIF(current_date,date_value,”M”).
Step 3: Calculate working days from today to a date value
To find the date diff in working day we use the NETWORKDAYS function. The syntax is:
=NETWORKDAYS(current_date,date_value)
Troubleshooting
A typical error you might be receiving when using the DATEDIF function is the #NUM! error. This happens in case that the start date value is before the end date. Make sure to use the DATEDIF correctly:
DATEDIF(current_date,date_value,"M")