Mastering Google Sheet formulas for dates helps you track timelines, calculate durations, and automate reporting without manual date math. These formulas turn static calendar entries into dynamic data that updates as your source information changes.
Use consistent date entry formats and locale settings so formulas behave predictably across team members and time zones.
Essential Date Functions Overview
Quick reference for the most practical Google Sheet formulas for dates and their core purposes.
| Function | Purpose | Example Input | Typical Use Case |
|---|---|---|---|
| TODAY() | Returns the current date | =TODAY() | Display today’s date, calculate age or days left |
| NOW() | Returns current date and time | =NOW() | Timestamps, elapsed time calculations |
| DATE(year, month, day) | Creates a valid date from components | =DATE(2024, 12, 31) | Build dates from year, month, day numbers |
| DAY(date) | Extracts day of month | =DAY("2024-12-31") | Analyze due dates, billing cycles |
| MONTH(date) | Extracts month number | =MONTH("2024-12-31") | Group data by month, quarterly reporting |
| YEAR(date) | Extracts year | =YEAR("2024-12-31") | Year-over-year comparisons, archiving |
| EDATE(start_date, months) | Shifts date by months | =EDATE("2024-01-31", 1) | Subscription renewals, forecast periods |
| DATEDIF(start_date, end_date, unit) | =DATEDIF("2024-01-01", TODAY(), "M") | Tenure, project age, contract duration | |
| WORKDAY(start_date, days, [holidays]) | =WORKDAY("2024-06-01", 10, {"2024-06-19"}) | Deadline planning, excluding holidays |
Calculating Differences and Durations with Date Formulas
Comparing dates is essential for deadlines, SLA tracking, and aging reports. Google Sheet formulas like DATEDIF and simple subtraction return the gaps between moments in days, months, or years.
For days-only precision, subtract one date from another and format the cell as a number. For human-friendly spans in years and months, combine DATEDIF units to create a clear duration description.
When end dates are missing, use TODAY() so calculations stay live as time passes, keeping dashboards and reports always current without manual updates.
Formatting and Display Options for Dates
How dates appear can change how easily teammates understand your sheet, so explicit formatting matters more than you might expect.
Apply custom date formats
Open Format > Number > More formats > Custom date and time format to control year, month, day, and time order. Presets like YYYY-MM-DD avoid locale confusion, while custom patterns like DD/MM/YYYY match regional expectations.
Handle errors and invalid dates
Use IFERROR around critical date formulas to return clean placeholders instead of error messages. Combine ISDATE with conditional formatting to highlight cells that do not contain valid dates so data issues surface quickly.
Combining Dates with Text and Conditional Logic
Smart reports often mix dates with descriptive text, and Google Sheet formulas make this straightforward while keeping results readable.
TEXT function for readable labels
Wrap dates with TEXT(date, "format") to produce strings like "Jan 2024" or "Monday, 09 Jun". These labels are perfect for chart axes, email subjects, or summaries where clarity trumps raw values.
Conditional decisions based on dates
Nest date checks inside IF statements to flag overdue items, qualify leads by recency, or route tasks based on due-week status. Pair with AND or OR to handle multi-condition rules cleanly and without messy nested chains.
Key Takeaways for Reliable Date Workflows
- Use TODAY() and NOW() for live clocks that always stay current
- Build unambiguous dates with DATE to avoid locale mix-ups
- Calculate differences with DATEDIF and plain subtraction for accurate durations
- Format dates explicitly so reports stay clear and consistent
- Combine TEXT, IF, and WORKDAY to create readable, business-aware logic
FAQ
Reader questions
How do I ensure my date formulas update automatically when I open the sheet?
Use volatile functions like TODAY() and NOW() so results refresh on each open, or set the sheet to calculation triggers that recalculate at regular intervals for time-sensitive metrics.
What should I do if imported dates are treated as text by Google Sheets?
Convert text to dates with DATEVALUE, split strings into components and rebuild them using DATE, or use Data > Split text to columns followed by formatting the column as a date to restore proper behavior.
Can I highlight cells based on whether a date is in the past or future?
Yes, apply conditional formatting with custom formulas such as =A2 TODAY() for future dates, then choose a clear color scheme so status is instantly visible.
How do I calculate workdays across multiple holiday calendars for different regions?
List all holiday dates in a helper range, then reference that range in the WORKDAY function so each region’s calendar is respected without rewriting formulas for every location.