Let’s say you’re planning a vacation and need to calculate how many days you’ll be away. It’s a simple task. You have your departure date in one cell and your return date in another. For instance, your departure date is in cell A2, and your return date is in cell B2.
There are multiple ways to write a formula in Google Sheets to find out the number of days between these two dates. So, let’s get started…
Subtract the Start Date from the End Date to Get the Count
The easiest way to get the number of days between two dates is to subtract the start date from the end date. You have the start date in cell A2 and the end date in cell B2. To find the total days in cell C2, type the formula =B2 – A2.
As a result, we have 13 days between 13-Feb-2024 and 26-Feb-2024. This is the best way to count the days between two dates quickly.
Count Days Difference Between TODAY and a Start Date
In the same way, you can use the TODAY function to get the count of days between a start date and today’s date. See in the below example:
Here, TODAY returns the current date (26-Jun-2024). In this example, cell A1 contains a date (13-Feb-2024).
The formula =TODAY() – A1 calculates the days between the current date and the date in cell A1. It subtracts the date in A1 from today’s date.
As of the current date, this formula calculates 134 days between 13-Feb-2024 and today.
Calculate Days IF Dates have time also
To calculate the number of days between two dates that include time and use the INT function to ensure that the result is an integer. Enter the below formula in cell C1 to calculate the difference:
=INT(A2-A1)
- A2-A1: Calculates the total difference in days, including the fractional part representing hours and minutes.
- INT: Rounds down the result to the nearest integer, effectively giving you the number of days between the two dates.
The start date and time are 13-Feb-2024 at 10:00 AM, and the end date and time is 26-Feb-2024 at 2:00 PM; the difference includes a fractional part (13.1667 days). But as you are using INT, it gives you 13 full days.
Calculate Days Between Two Dates with DATEDIF
The DATEDIF function calculates the difference between two dates. You can use it by entering =DATEDIF(start_date, end_date, “unit”), where “unit” can be “D” for days, “M” for months, or “Y” for years. As we need to get the count of days, you can use a formula like the one below:
This formula calculates the number of days between two dates. In this example, cell A1 contains the start date (13 February 2024), and cell B1 contains the end date (26 February 2024).
It uses the DATEDIF function, where “D” stands for days. By entering this formula in cell C1, the difference between the two dates is calculated, and the result is 13 days in cell C1. This shows the total number of days from the start to the end.
Get Days Between Two Dates with the DAYS
DAYS function calculates the difference between the two dates by subtracting the start date from the end date. Here’s the syntax: DAYS(end_date, start_date)
- end_date: The end date (the date you want to subtract from).
- start_date: The start date (the date you want to subtract).
=DAYS(B1, A1)
This will calculate the number of days between both dates. DAYS function here calculates the number of days between two dates. In this example, cell A1 contains the start date (13-Feb-2024), and cell B1 contains the end date (26-Feb-2024).
The formula =DAYS(B1, A1) subtracts the start date (A1) from the end date (B1) and returns the number of days between them. As shown in the image, the result is 13 days.
NETWORKDAYS and NETWORKDAYS.INTL to Get Days Between Dates
NETWORKDAYS calculates the working days between two dates, excluding weekends (Saturday and Sunday). Just use =NETWORKDAYS(start_date, end_date) to get the count of weekdays.
=NETWORKDAYS(A1, B1)
This will calculate the working days between the start and end dates, excluding weekends (Saturday and Sunday).
NETWORKDAYS.INTL calculates the number of working days between two dates, which you can customize to include weekends. Use =NETWORKDAYS.INTL(start_date, end_date, weekend) to set specific weekend days.
=NETWORKDAYS.INTL(A1,B1,1)
It also allows you to exclude custom dates while calculating the number of days between dates. See in the example below; we have two dates to exclude.
Here, the formula calculates the working days between the dates in cell A1 (13-Feb-2024) and cell B1 (26-Feb-2024).
The 1 specifies the default weekend (Saturday and Sunday). The range A3:A4 lists the holidays (19-Feb-2024 and 20-Feb-2024), which should be excluded from the working days count.
The formula calculates 8 working days between 13 February 2024 and 26 February 2024, excluding weekends (Saturdays and Sundays) and the holidays on 19 February 2024 and 20 February 2024.