Excel Guides

Calculating Elapsed Time with Excluded Periods in Excel

When calculating elapsed time, there may be periods that you want to exclude from the calculation. For example, you may want to calculate the amount of time that has elapsed since a project started, but exclude weekends and holidays. Excel has a number of built-in functions that can be used to calculate elapsed time with excluded periods.

The first step is to identify the start date and end date of the period that you want to calculate. For this example, we will use 1/1/2018 as the start date and 12/31/2018 as the end date. Next, we will use the WORKDAY function to calculate the number of workdays between the start and end dates. The WORKDAY function takes two arguments: the start date and the number of days. We will use 1 for the number of days argument, which will return the next workday after the start date:

=WORKDAY(START_DATE,1)

For our example, this returns 1/2/2018, which is the next workday after 1/1/2018.

We can then use the NETWORKDAYS function to calculate the number of workdays between our start and end dates, excluding weekends and holidays:

=NETWORKDAYS(START_DATE,END_DATE)

For our example, this returns 251, which is the number of workdays between 1/1/2018 and 12/31/2018.

Finally, we can use the DATEDIF function to calculate the number of days between our start and end dates:

=DATEDIF(START_DATE,END_DATE,"d")

For our example, this returns 365, which is the number of days between 1/1//2018 and 12//31//2018.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.