How to Count Days from 2 Dates in Excel Even Skip Weekends & Holidays

If you are looking for a quick and easy-to-follow solution to calculate the days of two dates, you have come to the right place!

Of course, everyone can easily count the require days within a week. What if there will be a need to count for up to a month or more with skipping weekends? Moreover, excluding unwanted dates/ holidays? Feeling the challenge? No worries, we are here to help!

Forget about schedule planner notebook, calendar and your pens. All you will need to do is to turn on Microsoft Excel to do the counting for you. Here are some simple tricks everyone can follow on how to calculate the difference of two dates via Microsoft Excel. Let’s get started!

Counting the Days Between Two Dates

Using the DATEDIF function

To use the DATEDIF function, start off by inserting your desired start date in cell A2, and an end date in cell B2, follow by the formula as shown below in cell C2:

 

=DATEDIF (A2, B2, “d”)

 

Note:

The syntax of the Excel DATEDIF function is understood as:

=DATEDIF (start_date, end_date, unit)

 

Using the subtract function

On occasion there will be days where it will be consider as due [negative number]. Unfortunately the DATEDIF function cannot be use as it will return the #NUM! error as seen in cell C4.

Here is where the subtract function takes the action. To find out the difference of cell B3,A3, insert the following formula in C3 to:

 

=(B3-A3)

Syntax: [End Date] – [Start Date]

 

Fun Fact:

Press [CTRL + `] (grave accent) to view the formula on your worksheet
Notice how Microsoft sorts out dates in serial number.

Hence, [4-Jan-17] – [5-Jan-17] will be generated as [42739] – [42740] = -1 !

 

Count the Days Between Two Dates (excluding weekends)

Using the NETWORKDAYS function

You’re doing great! Next is to find out how to calculate the number of days while skipping the weekends. To use the NETWORKDAYS function, insert a start date in cell A2, and an end date in cell B2, follow by inserting the formula as shown below in cell C2:

 

=NETWORKDAYS (A2, B2)

 

Note:

The syntax of the Excel DATEDIF function is understood as:

= NETWORKDAYS (start_date, end_date)

How to Skip Weekends and Holidays

Using the NETWORKDAYS function

Great! Are you up for the final challenge? Here’s to calculate the number of days while skipping the weekends AND holidays. Before we do the calculation, start off by inserting the holidays in your choice of specific range of cells. For now, we will be seeing from cell A7-B9. Once you’re done, proceed by inserting a start date in cell A3, and an end date in cell B3, follow by inserting the formula as shown below in cell C3:

 

=NETWORKDAYS (A3, B3,A7:A8)

 

The syntax of the Excel NETWORKINGDAYS function is understood as:

= NETWORKDAYS (start_date, end_date,[holidays])

 

That is the end of the tricks on how to calculate the number of days between two dates in variable.
We hope you’d liked it and it will be useful for your worksheet. Good Luck!

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *