This tutorial explains how to calculate a task’s duration, remaining days, and elapsed days from its start and end date in Google Sheets.

There are three sets of formulas for each calculation, allowing for the inclusion or exclusion of specific weekends and the option to include or exclude both the start and end dates in the calculation.

For example, if the start date is Monday, 15/4/2024, and the end date is Sunday, 21/4/2024, we can calculate the elapsed days, remaining days, and duration in three ways:

Legends:

• BI: Both Inclusive
• ESD: Excluding the Start Date
• EW: Excluding Sunday and Saturday Weekends

Note: For calculating durations for Gantt charts, it’s advisable to use either the formula that returns the duration with both dates inclusive or the formula that excludes weekends. Otherwise, you may encounter issues with the Gantt bar being one column short.

Calculating Duration, Remaining Days, and Elapsed Days (Start and End Inclusive)

Calculating Task Duration in Google Sheets

Task duration is the total number of days from the start date to the end date of a task, including both days.

In Google Sheets, you can use the following formula to calculate the duration of a task:

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, DAYS(end, start)+1)))

The formula assumes cell A2 contains the start date and B2 contains the end date.

You may wonder why use this formula instead of the simpler end_date - start_date + 1 to calculate the duration.

The above formula handles the issue of blank cells in start and end date cells and returns 0 when you accidentally enter a task end date that is earlier than the start date.

Formula Explanation:

• DAYS(end, start) + 1 returns the duration. The DAYS function is one of several date functions in Google Sheets, following the syntax DAYS(end_date, start_date). It returns the number of days between a start date and an end date. The DAYS function excludes the start date and includes the end date, so we added 1 to the output.
• MAX(0, DAYS(end, start)+1) ensures that the duration is always greater than 0. This helps when a user accidentally enters an end date earlier than the start date.
• IFERROR(MAX(0, DAYS(end, start)+1)) returns blanks when the duration encounters an error.

The task start date and end date are not just A2 and B2; instead, they are DATEVALUE(A2) and DATEVALUE(B2). We use the LET function to name these value expressions as ‘start’ and ‘end’ and use them in the duration calculation.

The DATEVALUE function returns an error when the source cell is empty. This prevents the formula from returning an incorrect duration. When the duration calculation encounters an error, IFERROR removes it.

Calculating Task Remaining Days in Google Sheets

Task remaining days are the number of days left until the task’s end date from today.

Here is the formula to calculate the remaining days of a task from a start and end date in cells A2 and B2, respectively, in Google Sheets:

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, DAYS(end, MAX(TODAY(), start))+1)))

Formula Explanation:

• MAX(TODAY(), start) returns the later of the start date or today’s date. This ensures that the calculation of remaining days starts from today if the task has already started, or from the start date if it is in the future.
• DAYS(end, MAX(TODAY(), start)) + 1 calculates the number of days between the date from the previous step and the task end date, including both days.
• MAX(0, DAYS(end, MAX(TODAY(), start)) + 1) ensures the formula always returns a value greater than or equal to 0. This accounts for situations where the end date is earlier than the start date (possibly due to an error) or when both dates are earlier than today’s date.
• IFERROR(MAX(0, DAYS(end, MAX(TODAY(), start)) + 1)) removes any errors that might arise, such as from empty cells.

Similar to the formula that calculates the task duration, this formula also uses the DATEVALUE function to handle blank cells. The DATEVALUE function returns an error when the source is empty, and IFERROR handles this error, ensuring the formula returns a correct and meaningful result.

We have seen how to calculate a task’s duration and remaining days properly in Google Sheets. Now, let’s proceed to the calculation of elapsed days.

Calculating Task Elapsed Days in Google Sheets

Task elapsed days are the number of days that have passed from the start date to today.

Since we know how to calculate the task duration and remaining days, calculating the task elapsed days is simple. You just need to subtract the remaining days from the duration. However, if you prefer a standalone formula, you can use this one:

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, DAYS(IF(end>=TODAY(), TODAY(), end+1), start))))

This formula returns the elapsed days from a start date in cell A2 and an end date in cell B2.

Formula Explanation:

• IF(end >= TODAY(), TODAY(), end + 1) returns today’s date if the end date is greater than or equal to today; otherwise, it returns the end date + 1. The +1 ensures that both days are included.
• DAYS(IF(end >= TODAY(), TODAY(), end + 1), start) calculates the number of days from the task start date to the date returned in the previous step.
• MAX(0, DAYS(IF(end >= TODAY(), TODAY(), end + 1), start)) ensures the elapsed days are greater than or equal to 0. This prevents negative results which may occur if the task start date is in the future or if the end date is earlier than the start date.
• IFERROR(MAX(0, DAYS(IF(end>=TODAY(), TODAY(), end+1), start))) DATEVALUE handles empty cells by returning an error, which IFERROR then removes, ensuring the formula works correctly even with blank cells.

Calculating Duration, Remaining Days, and Elapsed Days (Start Date Exclusive, End Date Inclusive)

In our previous examples, we utilized the DAYS function to calculate the duration, elapsed days, and remaining days. This function returns the number of days between two dates, excluding the start date and including the end date.

To ensure both dates are inclusive, we added 1 in the necessary sections of the formula. Here, you can use those formulas without the +1. Here they are:

Duration Calculation (Start Date Exclusive, End Date Inclusive):

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, DAYS(end, start))))

Remaining Days Calculation (Start Date Exclusive, End Date Inclusive):

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, DAYS(end, MAX(TODAY(), start)))))

Elapsed Days Calculation (Start Date Exclusive, End Date Inclusive):

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, DAYS(IF(end>=TODAY(), TODAY(), end), start))))

Calculating Duration, Remaining Days, and Elapsed Days (Excluding Weekends)

If you want to calculate the duration, remaining days, and elapsed days based on working days, you should replace the DAYS function in the above three formulas with the NETWORKDAYS.INTL function.

However, simply replacing the function name is not sufficient. You should adjust the arguments to match the syntax of NETWORKDAYS.INTL.

Syntax of the DAYS Function:

DAYS(end_date, start_date)

Syntax of the NETWORKDAYS.INTL Function:

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

This function returns the number of working days between two dates.

In this function, you can specify weekends using a series of seven 0s (workdays) and 1s (weekends), where the first and last numbers in the set represent Monday and Sunday, respectively.

In the following formulas, “0000001” represents Sunday as the weekend. To specify both Saturday and Sunday as weekends, replace this string with “0000011”.

Duration Calculation (Excluding Weekends):

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, NETWORKDAYS.INTL(start, end, "0000001"))))

Remaining Days Calculation (Excluding Weekends):

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, NETWORKDAYS.INTL(MAX(TODAY(), start), end, "0000001"))))

Elapsed Days Calculation (Excluding Weekends):

=LET(start, DATEVALUE(A2), end, DATEVALUE(B2), IFERROR(MAX(0, NETWORKDAYS.INTL(start, IF(end>=TODAY(), TODAY(), end), "0000001"))))

To specify holidays, enter them in a range and specify that range after the weekend parameter.

You can learn more about the NETWORKDAYS.INTL function in my function guide.

Resources

Here are a few related resources on calculating duration, remaining days, and elapsed days in Google Sheets.

• Elapsed Days and Time Between Two Dates in Google Sheets
• Days Remaining in Gantt Chart in Google Sheets
• Converting Time Duration to Day, Hour, and Minute in Google Sheets
• Group and Sum Time Duration Using Google Sheets Query
• Subtract a Duration from Duration in Google Sheets
• AGE_CALC – A Named Function to Calculate Age or Duration in Google Sheets