by Natalia Sharashova, updated on
Today's blog post is all about figuring out the difference between two dates in Google Sheets. You will see lots of DATEDIF formulas to count days, months and years, and learn how NETWORKDAYS is used to count workdays only even if your holidays are based on a custom schedule.
Lots of spreadsheets users find dates confusing, if not extremely difficult, to handle. But believe it or not, there are a few handy and straightforward functions for that purpose. DATEDIF and NETWORKDAYS are a couple of them.
As it happens with functions, their names suggest the action. The same goes for DATEDIF. It must be read as date dif, not dated if, and it stands for date difference. Hence, DATEDIF in Google Sheets calculates the date difference between two dates.
Let's break it down to pieces. The function requires three arguments:
Note. All units must be put to formulas the same way they appear above – in double-quotes.
Now let's piece all these parts together and see how DATEDIF formulas work in Google Sheets.
I have a small table to track some orders. All of them have been shipped in the first half of August – Shipping date – which is going to be my start date. There's also an approximate delivery date – Due date.
I'm going to calculate days – "D" – between shipping and due dates to see how long it takes for items to arrive. Here is the formula I should use:
=DATEDIF(B2, C2, "D")
I enter the DATEDIF formula to D2 and then copy it down the column to apply to other rows.
Tip. You can always calculate the entire column at once with a single formula using ARRAYFORMULA:
=ArrayFormula(DATEDIF(B2:B13, C2:C13, "D"))
Imagine there are a few months between two dates:
How do you count only days as if they belonged to the same month? That's right: by ignoring full months that have passed. DATEDIF calculates this automatically when you use the "MD" unit:
=DATEDIF(A2, B2, "MD")
The function subtracts elapsed months and counts remaining days.
Another unit – "YD" – will aid for when dates have more than a year between them:
=DATEDIF(A2, B2, "YD")
The formula will subtract years first, and then calculate remaining days as if they belonged to the same year.
There is a special case when you need to count only working days in Google Sheets. DATEDIF formulas won't be much of a help here. And I believe you will agree that subtracting weekends manually is not the most elegant option.
Luckily, Google Sheets has a couple of not-so-magic spells for that :)
The first one is called NETWORKDAYS. This function calculates the number of working days between two dates excluding weekends (Saturday and Sunday) and even holidays if necessary:
Note. If this date is not a holiday, it is counted as a working day.
Note. If this date is not a holiday, it is counted as a working day.
To illustrate how it works, I will add a list of holidays that take place in-between shipping and due dates:
So, column B is my start date, columns C – end date. Dates in column E are the holidays to consider. Here is how the formula should look:
=NETWORKDAYS(B2, C2, $E$2:$E$4)
Tip. If you're going to copy the formula to other cells, use absolute cells references for holidays to avoid errors or incorrect results. Or consider building an array formula instead.
Have you noticed how the number of days decreased compared to the DATEDIF formulas? Because now the function automatically subtracts all Saturdays, Sundays, and two holidays that take place on Friday and Monday.
Note. Unlike DATEDIF in Google Sheets, NETWORKDAYS counts start_day and end_day as workdays unless they are holidays. Hence, D7 returns 1.
If you have a custom weekend schedule, you will benefit from another function: NETWORKDAYS.INTL. It lets you count working days in Google Sheets based on personally set weekends:
Note. NETWORKDAYS.INTL in Google Sheets also counts start_day and end_day as workdays unless they are holidays.
Tip. This way is perfect for when your days off are scattered all over the week.
Mask is a seven-digit pattern of 1's and 0's. 1 stands for a weekend, 0 for a workday. The first digit in the pattern is always Monday, the last one – Sunday.
For example, "1100110" means that you work on Wednesday, Thursday, Friday, and Saturday.
Note. The mask must be put in double-quotes.
Use one-digit numbers (1-7) that denote a pair of set weekends:
Number | Weekend |
1 | Saturday, Sunday |
2 | Sunday, Monday |
3 | Monday, Tuesday |
4 | Tuesday, Wednesday |
5 | Wednesday, Thursday |
6 | Thursday, Friday |
7 | Friday, Saturday |
Or work with two-digit numbers (11-17) that denote one day to rest within a week:
Number | Weekend day |
11 | Sunday |
12 | Monday |
13 | Tuesday |
14 | Wednesday |
15 | Thursday |
16 | Friday |
17 | Saturday |
This function may seem complicated because of all those numbers, but I encourage you to give it a try.
First, just get a clear understanding of your days off. Let's make it Sunday and Monday. Then, decide on the way to indicate your weekends.
If you go with a mask, it will be like this – 1000001:
=NETWORKDAYS.INTL(B2, C2, "1000001")
But since I have two weekend days in a row, I can use a number from the tables above, 2 in my case:
=NETWORKDAYS.INTL(B2, C2, 2)
Then simply add the last argument – refer to holidays in column E, and the formula is ready:
=NETWORKDAYS.INTL(B2, C2, 2, $E$2:$E$4)
Sometimes months matter more than days. If this is true for you and you prefer getting the date difference in months rather than days, let Google Sheets DATEDIF do the job.
The drill is the same: the start_date goes first, followed by the end_date and "M" – that stands for months – as a final argument:
=DATEDIF(A2, B2, "M")
Tip. Don't forget about the ARRAUFORMULA function that can help you count months on all rows at once:
=ARRAYFORMULA(DATEDIF(A2:A13, B2:B13, "M"))
You may not need to count months throughout all years in-between start and end dates. And DATEDIF lets you do that.
Just use the "YM" unit and the formula will subtract whole years first, and then count the number of months between dates:
=DATEDIF(A2, B2, "YM")
The last (but not least) thing to show you is how Google Sheets DATEDIF calculates the date difference in years.
I'm going to calculate the number of years couples have been married based on their wedding dates and today's date:
As you may have already guessed, I will use the "Y" unit for that:
=DATEDIF(A2, B2, "Y")
All these DATEDIF formulas are the first to try when it comes to calculating days, months, and years between two dates in Google Sheets.
If your case can't be solved by these or if you have any questions, I encourage you to share them with us in the comments section below.
Table of contents