Introduction
Google Sheets treats every date as a numeric value, which means you can apply arithmetic, logical tests, and a rich set of built‑in functions to manipulate time‑related data. Whether you need to count the days until a deadline, determine the next payday, or compute the number of business days between two milestones, mastering the core date functions will turn a simple spreadsheet into a powerful scheduling engine. In this guide we will explore the most essential date functions, illustrate each with ready‑to‑copy formulas, and show how they interconnect to solve real‑world problems such as project planning, birthday reminders, and holiday calculations. By the end, you’ll be equipped to handle any date‑driven task in Google Sheets with confidence.
Understanding Date Serial Numbers
Google Sheets stores dates as serial numbers—the count of days since December 30, 1899. This underlying representation is why functions like DATE, YEAR, and DATEVALUE can be combined seamlessly.
- DATE(year, month, day) creates a serial number from separate components. Example: =DATE(2026,3,22) returns the serial for today.
- DATEVALUE(text) converts a date written as text into its serial. Example: =DATEVALUE(“12/31/2025”) yields 44718.
- YEAR(date), MONTH(date), DAY(date) extract individual parts from a serial. Example: =YEAR(A2) returns the year of the date in A2.
Because the serial is just a number, you can add or subtract days directly: =A2+7 gives the date one week later, while =A2-30 moves back a month (approximately).
Basic Date Extraction and Formatting
Often you need to display dates in a specific layout without altering the underlying value. The TEXT function formats a serial number into any pattern you choose.
- TEXT(date, “dddd, mmmm d, yyyy”) produces a full textual representation. Example: =TEXT(TODAY(), “dddd, mmmm d, yyyy”) → “Tuesday, March 22, 2026”.
- TEXT(date, “mm/dd/yy”) creates a compact US style. Example: =TEXT(A3, “mm/dd/yy”).
- WEEKDAY(date, 2) returns the ISO day of week (1 = Monday). Use it to flag weekends: =IF(WEEKDAY(A4,2)>5,”Weekend”,”Weekday”).
Combining extraction with IF statements lets you build dynamic labels, such as “Quarter 1” versus “Quarter 2” using ROUNDUP(MONTH(date)/3,0).
Calculating Differences and Intervals
When you need to know how many days, months, or years separate two events, Google Sheets offers dedicated functions that respect calendar rules.
- DAYS(end_date, start_date) returns the simple day count. Example: =DAYS(“2026-12-31”, TODAY()) tells you how many days remain in the year.
- NETWORKDAYS(start, end, [holidays]) counts only Monday‑to‑Friday days, optionally excluding holiday dates. Example: =NETWORKDAYS(A5, B5, $D$2:$D$10).
- DATEDIF(start, end, “Y”), “M”, “D” calculate whole years, months, or days between dates. Example: =DATEDIF(Birthday, TODAY(), “Y”) yields the current age.
- EDATE(start_date, months) shifts a date by a given number of months, automatically handling month‑end overflow. Example: =EDATE(TODAY(), 6) gives the same day six months from now.
These functions can be nested; for instance, to find the number of business days until the next birthday: =NETWORKDAYS(TODAY(), DATE(YEAR(TODAY())+(MONTH(TODAY())>MONTH(B2)), MONTH(B2), DAY(B2))).
Working Days, Holidays, and Project Planning
Project managers frequently need to forecast completion dates while respecting non‑working days. The WORKDAY family provides a concise solution.
- WORKDAY(start, days, [holidays]) returns the date after adding a specified number of workdays. Example: =WORKDAY(TODAY(), 15, $D$2:$D$10) skips weekends and listed holidays.
- WORKDAY.INTL(start, days, [weekend], [holidays]) lets you define custom weekend patterns (e.g., Saturday‑Sunday, Friday‑Saturday). Example: =WORKDAY.INTL(A6, 10, “0000011”) treats Friday and Saturday as weekends.
- Combine WORKDAY with IF to flag overdue tasks: =IF(TODAY()>WORKDAY(start_date, duration, holidays),”Overdue”,”On Track”).
By maintaining a separate holiday list (one column with each holiday’s serial number), all the above formulas stay accurate year after year, and you can reuse the list across multiple sheets via named ranges.
Conclusion
Mastering Google Sheets’ date functions transforms raw timestamps into actionable insights. Starting with the serial‑number foundation, you learned how to construct dates, extract components, and format them for clear presentation. You then explored precise interval calculations—days, months, years, and business days—using DAYS, DATEDIF, and NETWORKDAYS. Finally, you saw how WORKDAY and WORKDAY.INTL empower project planners to schedule tasks around weekends and custom holidays. By integrating these tools—paired with a well‑maintained holiday list—you can automate deadline tracking, age calculations, and any time‑sensitive workflow. Apply the examples provided, adapt them to your own data, and let Google Sheets handle the calendar so you can focus on delivering results.
