This guide discusses how to count days in Google Sheets. After all, efficient date tracking is essential for so many reasons. It’s used for managing projects, meeting deadlines, and common accounting tasks. Here we discuss built-in formulas that help you easily determine how many days remain until a deadline, how long a task has been in progress, or how many working days are left in a given period.
Beyond project management, date calculations play a crucial role in financial planning, payroll processing, and even age tracking. From determining an employee’s tenure to calculating interest accrual, these formulas offer precision and efficiency for business and personal needs.
Below, we’ll explore how to use Google Sheets and Excel to count days between dates, calculate age, and automate financial and billing calculations.
How to Count Days: Google Sheets
Accurate date calculations are essential for tracking deadlines, financial projections, and project timelines. Google Sheets offers several methods to count days in a spreadsheet. And note that we also have a guide on how to calculate days between dates in Google Sheets.
Basic Day Count
To find the difference between two dates:
- Enter a start date in one cell (e.g., A2) and an end date in another (e.g., B2).
- Use the formula:
=B2-A2
This subtracts the earlier date from the later one, returning the number of days between them.
Counting Business Days Only
To count weekdays, excluding weekends, use:
=NETWORKDAYS(A2, B2)
This formula counts only Monday through Friday, excluding Saturdays and Sundays. The syntax for NETWORKDAYS is pretty simple. It’s just (start date, end date, holiday list).
Counting Business Days with Holidays: For accuracy when holidays like Independence Day or Columbus Day impact schedules, add a holiday list (e.g., in cells D2:D10) and use:
=NETWORKDAYS(A2, B2, D2:D10)
Note that a list allows you to choose which holidays to include. While Google may have removed Pride Month, Juneteenth, and Black History Month from their calendars, you can still add these to a list manually. That allows you to create custom date-tracking solutions for your business, school, or household.
Counting Specific Days of the Week: If you need to count occurrences of a specific weekday (e.g., Mondays), use:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&B2)), "DDD")="Mon"))
Google Sheets’ built-in functions make date calculations seamless, ensuring precision in planning, payroll, and project management.
Using Date Calculations for Age and Financial Projections
One of the most common applications of date counting is determining a person’s age. Whether for HR records, customer profiles, or legal documents, knowing someone’s exact age can be essential. Instead of manually calculating birthdays, you can use a simple formula in both Google Sheets and Excel:
=DATEDIF(A2, TODAY(), "Y")
This formula subtracts the birthdate (A2) from today’s date and returns the person’s age in years. If more precision is needed, you can modify the formula to include months like (numbers, not names like January, February, and March) and days (again the count, not specific days like Monday or Friday):
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days"
Beyond age calculations, financial professionals frequently use date functions to track loan terms, investment maturity, and interest accrual. By determining how many days have passed since a financial transaction, businesses can calculate late fees, interest charges, or payment due dates with accuracy.
The DAYS function in Excel, for example, allows users to track the duration of investments:
=DAYS(B2, A2)
This tool helps financial analysts project earnings, calculate depreciation, and monitor the longevity of financial agreements.
Automating Billing and Invoicing with Date Formulas
For businesses that operate on recurring billing cycles or invoicing schedules, date calculations help automate payment tracking. If invoices are due within a set period after issuance, formulas can determine the due date and flag overdue payments.
Automating these calculations not only saves time but also reduces human error, ensuring smooth financial operations and helping businesses maintain healthy cash flow. Whether managing invoices for a small business or handling large-scale enterprise billing, Google Sheets and Excel provide the tools needed to streamline payment tracking.
Understanding Errors When Counting Dates in Google Sheets
Errors in date calculations usually occur due to formatting issues, incorrect inputs, or problems with the logic of the formula. One of the most common causes is when a date is stored as text rather than as a recognized date value. If a cell appears to contain a date but does not align properly or does not function in calculations, it is likely formatted as text. To fix this, you can check the cell’s format settings and convert it to a proper date format.
Another common issue arises when the order of dates is incorrect. Some functions, such as DATEDIF, require the earlier date to be listed first; otherwise, they may return an error or an unexpected result. If your formula depends on a range of dates, double-check that the start and end dates are correctly positioned.
If you are using a function that counts business days, such as NETWORKDAYS, an error may appear if the start date is later than the end date. In such cases, adjusting the logic to handle reversed dates can prevent the issue. Additionally, if your formula references a holiday list and one of the holiday entries is not a valid date, the function may fail. Ensuring that all referenced dates are correctly formatted will help maintain accuracy.
Lastly, if an error such as #VALUE! appears, it often indicates that one of the referenced cells contains an invalid entry—such as text where a date is expected. Checking for inconsistencies in data entry and using functions like DATEVALUE to convert text-based dates into valid date values can resolve these issues.
Note that you can use the ISERROR function to quickly highlight or hide errors in Google Sheets and Microsoft Excel.
Common Questions
How Do I Exclude Weekends When Counting Days in Google Sheets?
To count the number of days between two dates while skipping weekends, use the NETWORKDAYS function. This function automatically excludes Saturdays and Sundays from the total. If you need to factor in holidays as well, you can add a list of holiday dates and reference them in the formula. This approach is commonly used for tracking work schedules, payroll, and project deadlines where only business days matter.
How Do I Count Months or Years Instead of Days?
Instead of counting days, you can use the DATEDIF function to measure the difference between two dates in months or years. This function allows you to specify whether you want the result in complete months or years. If you need a detailed breakdown, such as showing both years and months, you can combine multiple calculations. This is useful for tracking age, employment duration, and the length of contracts.
How Can I Determine The Number of Days Left Until a Deadline Automatically?
To create an automatic countdown to a deadline, use the TODAY function. This function updates daily, allowing you to track how many days remain until a specific date. If the deadline has passed, you can use the IF function to display a message like “Overdue” instead of showing a negative number. For better visualization, you can apply conditional formatting to highlight approaching deadlines in different colors, making it easier to manage tasks and schedules.
How Do I Display Day Counts Without Showing Actual Dates?
If you want to present only the number of days between two dates without displaying the actual dates, you can use a formula that subtracts the two values directly. This method is helpful when tracking the duration of projects, employee tenure, or event countdowns without exposing sensitive date details.
To ensure clarity, you can format the result to display as plain numbers instead of dates. If negative values might appear—for example, when the start date is later than the end date—you can use the ABS function to always show a positive number.
For situations where you want to display the day count as part of a custom message, functions like TEXT or CONCATENATE can be used to structure the output in a readable way. For example, you might display a result as “15 days remaining” instead of just the number 15. If the day count needs to update dynamically, using TODAY will ensure that the calculation adjusts automatically as time passes.
By understanding and applying these date functions, businesses and individuals can enhance efficiency, minimize missed deadlines, and improve financial accuracy. Whether tracking projects, age, financial transactions, or invoices, mastering date calculations is a valuable skill for anyone managing time-sensitive data.
Note that we also have several free calendar spreadsheet templates, if you’d like to track days and dates visually.
