Google Sheets Date Difference in Days: Formulas and Tips

To find the number of days between two dates in Google Sheets, subtract the earlier date from the later one: with a start date in A2 and an end date in B2, enter =B2-A2. Format the result as a number if it appears as a date; the answer counts elapsed days, not both endpoints.

How date differences work in Google Sheets

Google Sheets stores dates as numeric values, so subtraction works directly. Each whole-number step represents one calendar day. If A2 contains April 10 and B2 contains April 15 of the same year, subtracting the first value from the second returns 5. The formula does not need to know the month length or whether a year is a leap year; Sheets handles those details when it interprets valid dates.

This simple calculation is often the clearest choice for elapsed calendar days. It also allows a negative result if the end date is earlier than the start date. That can be useful for spotting reversed dates, or it can be a problem if a report expects only positive values. Decide what the result should mean before choosing a formula.

A cell may contain a date that looks right but is actually text. Text values cannot always be subtracted as dates, and they may produce an error or an unexpected result. Dates entered through the date picker or recognized by the spreadsheet’s locale are more reliable than ambiguous text copied from another system.

Use DATEDIF or DAYS when the formula reads more clearly

Subtraction is not the only option. Google Sheets also supports DATEDIF, which returns the difference in a chosen unit. For days, the unit argument is “D”. With a start date in A2 and an end date in B2, use =DATEDIF(A2,B2,"D"). This returns 5 for April 10 to April 15.

DATEDIF can be handy when a formula already uses other units, such as months or years. For a plain day count, though, subtraction is usually easier to inspect. DATEDIF expects the start date first and the end date second. If the end date is earlier, it can return an error rather than a negative count, so it is not a direct replacement when negative differences are meaningful.

Another built-in option is DAYS, whose arguments are ordered end date first, then start date. With the same dates in A2 and B2, enter =DAYS(B2,A2). It returns 5. The argument order is worth noting: DAYS uses the opposite order from DATEDIF. Choose one approach and keep its argument order consistent when copying formulas down a column.

Choose between elapsed days and inclusive date counts

Subtracting dates counts the number of day boundaries separating them. For example, April 10 to April 15 is five days apart. Many duration questions use this meaning: how many days until a due date, how long between two events, or how many nights separate arrival and departure.

Some tasks instead ask how many calendar dates are included in a range. If a five-day event runs from April 10 through April 15 and both the first and last dates count, there are six dates in the range. With a start date in A2 and an end date in B2, add 1: =B2-A2+1. The result is 6 for those dates.

Elapsed time or days between dates: subtract the start from the end.
Number of dates including both endpoints: subtract and add 1.
Days remaining until a deadline: subtract today’s date from the deadline; a negative answer means the date has passed.

Neither convention is universally correct. A hotel stay, for example, is often measured in nights, so arrival and departure dates are not both counted as nights. A calendar schedule may count every date touched by an event. Label the result clearly so readers know whether endpoints are included.

Account for times stored with dates

A Google Sheets cell can hold a time as well as a date. Under the hood, the time is a fraction of a day. Therefore, subtracting two date-time values can return a decimal: a difference of 12 hours is 0.5, and a difference of 6 hours is 0.25. This is accurate when you want a precise elapsed duration, but it may look surprising if you expected a whole-number date count.

If the cells contain times but you want to compare only their calendar dates, remove the time portion before subtracting. With date-time values in A2 and B2, use =INT(B2)-INT(A2). INT removes the fractional time portion from each value, leaving the date portion. This counts date boundaries, so two times on the same date return 0 even if several hours passed.

If the goal is to report whole elapsed days rather than calendar dates, consider what should happen to partial days. Rounding can turn a partial day into a full day, while truncating or taking the integer part can discard it. For instance, 23 hours is less than one full elapsed day, while 25 hours is one full day plus an hour. A business rule should determine whether to round up, round down, or preserve a decimal result.

Calculate working days instead of calendar days

Calendar-day subtraction includes weekends and holidays. For a work schedule, use NETWORKDAYS, which counts weekdays between two dates and includes both the start and end dates when they fall on working days. With a start date in A2 and an end date in B2, enter =NETWORKDAYS(A2,B2). A Monday-to-Friday range from Monday through Friday returns 5, rather than the 4 returned by ordinary subtraction.

To exclude holidays too, place holiday dates in D2:D10 and provide that range as the third argument: =NETWORKDAYS(A2,B2,$D$2:$D$10). The dollar signs keep the holiday range fixed when you fill the formula down. Make sure the holiday entries are real date values, not text strings, and remove duplicates if the list might contain them.

NETWORKDAYS assumes a standard Saturday-and-Sunday weekend. If the workweek follows a different pattern, Google Sheets provides NETWORKDAYS.INTL, which allows a weekend pattern to be specified. Check the function’s help in Sheets when setting that pattern, since the weekend argument has defined codes and can also be supplied as a seven-character pattern. These functions answer a different question from simple subtraction: they count scheduled workdays, not elapsed calendar days.

Format the result and diagnose common errors

After entering a subtraction formula, Sheets may display the result in a date format because the result cell inherited date formatting. If the underlying answer is 5, a date format can make it appear as a date rather than a day count. Select the result cells and choose Format > Number > Number. For a plain integer, reduce decimal places using the toolbar controls if needed. If you want to display a date, use Format > Number > Date instead.

When a formula returns an error or a strange value, check these common causes:

Dates are stored as text: A value imported from a CSV or copied from a website may not be recognized as a date. Check the cell’s alignment and formatting, then re-enter or convert the value using a method suited to its format.
Start and end dates are reversed: Direct subtraction returns a negative number; DATEDIF may return an error. Confirm which column is meant to contain the start date.
The result cell has a date format: Change its number format to Number to see the day count.
A time component is present: A decimal result can be a real fraction of a day, not a calculation mistake.
The locale interprets dates differently: Dates such as 04/05/2025 can mean different days in different regional settings. Check File > Settings for the spreadsheet’s locale, and use unambiguous dates when sharing data.

For an automatically updating count, TODAY returns the current date. With a deadline date in B2, this formula calculates the number of days from today to that deadline: =B2-TODAY(). TODAY recalculates as the sheet updates, so the displayed number changes over time. It does not include the current date as an extra day; add 1 only if the task specifically calls for an inclusive count.

Handle blank cells in a date list

When you fill a date-difference formula down a table, blank rows can produce confusing outputs. A blank cell may be treated like a zero-valued date in calculations, which can create a large or otherwise misleading number. For a clean report, check that both date cells are present before calculating.

With a start date in A2 and an end date in B2, this formula leaves the result blank until both cells contain values: =IF(OR(A2="",B2=""),"",B2-A2). It is useful in a tracking sheet where new records are added over time. If your sheet should flag incomplete records instead, replace the blank result with a message that makes the missing date obvious.

For larger tables, use consistent date columns and apply the same formula down the result column. A header such as “Elapsed days” or “Inclusive days” communicates the counting rule better than a generic “Days” heading.

Frequently Asked Questions

How do I calculate the number of days between two dates in Google Sheets?
Put the start date in one cell and the end date in another, then subtract the start from the end. For dates in A2 and B2, use =B2-A2. Format the result as a number if it displays as a date.

Does Google Sheets count the start date and end date?
Ordinary subtraction counts the distance between dates and does not count both endpoints as separate dates. Add 1 when the question requires an inclusive count of every calendar date from start through end.

Why is my date difference a decimal?
One or both cells probably include a time. Sheets stores time as part of a day, so a partial day appears as a decimal. If you only want the difference between calendar dates, use INT on each date-time value before subtracting.

How can I count weekdays between two dates?
Use NETWORKDAYS with the start date first and end date second. It excludes Saturdays and Sundays and counts eligible weekdays at both endpoints. Add a holiday-date range as its third argument when those dates should not count.

Why does the result show a date instead of a number?
The result cell is likely formatted as a date. Select it and choose Format > Number > Number. The formula may already be calculating correctly; changing the display format reveals the numeric day count.

Which formula should you use?

For most date-difference questions, subtraction is the shortest and easiest method. Use DATEDIF when its unit-based structure fits a larger formula, DAYS when its named function reads more clearly, and NETWORKDAYS when weekends or holidays must be excluded. Add 1 only for an inclusive count, and remove time fractions only when the question concerns dates rather than precise elapsed time.

These functions and basic date-subtraction behavior are also familiar to Excel users, although menu labels and some spreadsheet details differ between products. In Google Sheets in a browser, the key formatting route is Format > Number. The most important practical choice is not the function name but defining what “days between” means for the task and making that definition visible in the sheet.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.