Date Formula in Google Sheets: A Practical Guide
Master date formulas in Google Sheets with practical examples. Learn TODAY, DATE, EDATE, and EOMONTH to build schedules, dashboards, and time-based calculations.
Date formulas in Google Sheets let you create, compare, and advance dates automatically. You can build schedules, track deadlines, and compute durations using functions like TODAY, DATE, EDATE, and EOMONTH. This guide walks through real-world examples, explains how to format and validate dates, and shows best practices for handling time zones and locale differences in sheets.
What makes date formulas powerful in Google Sheets
Date formulas transform plain text into real date values and enable powerful time-based calculations. They are essential for schedules, deadlines, aging analyses, and dashboards. According to How To Sheets, the most-used date functions are TODAY, DATE, and EDATE because they automate time-based workflows across projects and planning scenarios. This section introduces core concepts and a few practical examples to get you started.
=TODAY() =DATE(2026, 3, 14) Dates in Sheets are stored as serial numbers; formatting only changes how the value appears. You can add days directly, e.g., =A2+30, to roll dates forward by a month in most cases.
Creating and formatting dates from parts
Creating a date from year, month, and day parts is a common task when importing data or building schedules. The DATE function ensures the components are treated as a calendar date, not plain text. Once created, you can format it for display in any pattern you need.
=DATE(2026, 4, 15)=TEXT(DATE(2026, 4, 15), "yyyy-mm-dd")=TEXT(DATE(2026, 4, 15), "ddd, mmm d, yyyy")Formatting is flexible: you can show ISO-like forms, full month names, or compact numeric dates depending on your locale and the audience.
Date arithmetic: moving dates with months and days
Date arithmetic lets you shift dates forward or backward without manual calculation. The EDATE function moves a date by a specified number of months, while EOMONTH returns the last day of the resulting month. These are crucial for due-date calculations and monthly reports.
=EDATE(DATE(2026, 3, 1), 6)=EOMONTH(DATE(2026, 3, 15), 0) // end of the same month=DATEDIF(DATE(1990, 5, 10), TODAY(), "y") // age in yearsThese formulas handle month rollovers automatically, which reduces errors when calculating futures or anniversaries.
Working with days, weeks, and business days
Beyond simple date math, you often need to count business days or skip weekends. Google Sheets offers NETWORKDAYS and WORKDAY family functions (including INTL variants) to handle these tasks. They also support holidays via optional ranges, making them ideal for project timelines and staffing plans.
=NETWORKDAYS.INTL(A2, B2)=WORKDAY.INTL(A2, 10, "0000011")=WORKDAY(TODAY(), 7) // next 7 workdays from todayIf you need to customize weekends, you can leverage the INTL variants to reflect your locale and organizational rules.
Handling date inputs and locale considerations
Dates can arrive as text or numbers. If a date is stored as text, parsing with DATEVALUE often fixes issues, but locale matters. ISO formats (YYYY-MM-DD) are generally safest when data is shared across regions.
=DATEVALUE("2026-03-20")=DATEVALUE("20/03/2026") // may fail if your locale expects month-firstIf you receive mixed inputs, you can guard with DATEVALUE or a conditional that checks if the value is already a date. This reduces errors when feeding dashboards or reports.
Troubleshooting date formulas: common pitfalls and fixes
Date-related errors are often caused by treating dates as text, incorrect formatting, or locale mismatches. A robust practice is to always store dates as date values and convert inputs with DATEVALUE only when necessary. If a cell contains a non-date, you can coerce it or fallback to a default.
=DATEVALUE(A2)=IF(ISNUMBER(A2), A2, DATEVALUE(A2)) // robust input handlingRegularly validate critical date columns with simple tests (e.g., ensuring end dates come after start dates). This catches misformatted data before it propagates into dashboards.
Advanced date work: dynamic dashboards and time intelligence
For dynamic dashboards, combine date formulas with conditional logic to surface time-sensitive insights. Use TODAY() to highlight overdue tasks or to compute aging buckets. You can also compare dates to build “days remaining” indicators for planning.
=IF(TODAY()=A2, "Today", IF(A2<TODAY(), "Overdue", "Upcoming"))=DATEDIF(A2, TODAY(), "d") // days remaining/elapsedThese techniques enable real-time dashboards that refresh when the sheet recalculates, reducing manual updates and keeping teams aligned. The How To Sheets Team recommends applying these patterns in service level dashboards and academic calendars to maximize visibility.
Steps
Estimated time: 30-60 minutes
- 1
Prepare date column
Create a dedicated column for dates and set the format to Date. This ensures all subsequent formulas operate on true date values rather than text.
Tip: Use Data > Data validation to restrict inputs to dates. - 2
Create base date values
Populate your sheet with base dates using DATE(year, month, day) or TODAY() for dynamic dates. Ensure cells are recognized as dates by confirming the alignment and formatting.
Tip: Prefer ISO date inputs when importing data. - 3
Apply common date formulas
Experiment with DATE, TODAY, EDATE, EOMONTH, and DATEDIF to build calendars, schedules, and aging analyses.
Tip: Document your date logic so others can follow. - 4
Handle locale and text dates
Convert text to dates with DATEVALUE when necessary and test formatting across locales.
Tip: Use ISO formats to minimize locale ambiguity. - 5
Incorporate business-day logic
Add NETWORKDAYS or WORKDAY to exclude weekends and holidays from calendars.
Tip: List holidays in a range and reference it in the formula. - 6
Validate and automate
Build checks to ensure end dates are after start dates and alerts for anomalies.
Tip: Create conditional formatting to highlight date issues.
Prerequisites
Required
- Required
- Basic knowledge of Google Sheets formulasRequired
- Access to a web browser with internet connectivityRequired
Optional
- Familiarity with date formats (ISO preferred: YYYY-MM-DD)Optional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Insert current dateIn a date cell to insert today's date | Ctrl+; |
| Paste values to keep dates as valuesPaste special: paste values only | Ctrl+⇧+V |
| Open format cells dialog to adjust date formatFormat cells > Number > Date | Ctrl+1 |
FAQ
What is the difference between TODAY() and NOW() in Google Sheets?
TODAY() returns the current date with no time component, while NOW() returns the current date and time. Both recalculate when the sheet recalculates, which makes them useful for time-aware dashboards and deadlines.
TODAY gives you just the date; NOW includes time as well.
Can I use date formulas with text dates in different locales?
Yes, but locale affects how dates are parsed and displayed. ISO formats (YYYY-MM-DD) reduce ambiguity. If you receive locale-dependent dates, use DATEVALUE to convert text to a date and set the sheet locale appropriately.
Locale can change how dates are read; use ISO formats when possible.
How do I calculate age from a birthdate in Google Sheets?
You can use DATEDIF or YEARFRAC to compute age. A common approach is =DATEDIF(birthdate, TODAY(), "Y") to get full years.
Use DATEDIF with today to calculate age in years.
How can I add or subtract months from a date?
Use EDATE(date, months). For example, =EDATE(DATE(2026,3,1), 6) adds six months.
EDATE is the go-to for month-based shifts.
Why isn’t my date showing as a date?
Dates can show as text if imported data isn’t recognized as date values. Convert with DATEVALUE or reformat cells as Date to fix
Make sure your date cells are real dates, not text.
What’s a quick way to count business days between two dates?
Use NETWORKDAYS or NETWORKDAYS.INTL. For example, =NETWORKDAYS(A2,B2) counts weekdays between two dates, optionally excluding holidays.
NETWORKDAYS helps you count working days easily.
The Essentials
- Use TODAY, DATE, and EDATE for date creation and shifts
- Dates are stored as serial numbers—formatting changes display, not value
- Leverage NETWORKDAYS and WORKDAY for business-day planning
- Convert text to date with DATEVALUE to avoid parsing errors