Practical Guide to Google Sheets Date Functions
Learn to use Google Sheets date functions like DATE, TODAY, and EDATE with clear examples, best practices, and real-world workflows for students, professionals, and small teams.
The google sheets date function family includes DATE, DATEVALUE, TODAY, NOW, EDATE, and EOMONTH. Use DATE(year, month, day) to construct fixed dates, DATEVALUE(text) to convert date text into a date, and TODAY() to fetch the current date. For relative changes, combine TODAY with arithmetic and functions like EDATE for month shifts. This foundation enables robust date-driven workbooks.
The date function family in Google Sheets
Dates are a core pillar of many Google Sheets workflows, from scheduling and budgeting to dashboards. The date function family includes DATE, DATEVALUE, TODAY, NOW, EDATE, EOMONTH, and DATEDIF, among others. Understanding how these functions interact with numeric date serials in Sheets is essential for reliable calculations. By combining these functions with arithmetic, you can create dynamic calendars, timelines, and deadline trackers. According to How To Sheets, mastering date functions reduces data-entry errors and makes date-driven reporting much more predictable.
=DATE(2026,3,7) // constructs a fixed date: 2026-03-07
=TODAY() // returns today's dateNotes: DATE returns a serial date number that can be formatted. TODAY returns the current date as a value that updates when the sheet recalculates.
Creating date values with DATE and DATEVALUE
Constructing dates programmatically and converting textual dates into real date values are two common needs. The DATE function lets you build a date from year, month, and day components, while DATEVALUE converts a date represented as text into a date value that Sheets can manipulate. This distinction is important when importing data from external sources where dates arrive as strings.
=DATE(2026,12,25) // yields 2026-12-25
=DATEVALUE("2026-12-25") // converts text to a date serialExplanation: DATE uses numeric inputs; DATEVALUE expects a textual date in a recognizable format. When date data comes as text, convert it first, then apply other date calculations.
Getting the current date and time: TODAY and NOW
For time-bound workflows, TODAY and NOW provide fresh date and datetime values. TODAY returns the date portion only, while NOW includes the current time. These functions are ideal for stamps, due-date calculations, and time-aware reports. When used in calculations, ensure the cell is formatted to display only the date or date-time as needed.
=TODAY() // e.g., 2026-03-07
=NOW() // e.g., 2026-03-07 14:35:12Tips: Avoid relying on NOW for historical batch calculations unless you explicitly capture the time at a specific moment.
Adding or subtracting days, months, and years
Date arithmetic is straightforward: you can add or subtract days by simply adding or subtracting numbers to a date. For more complex shifts, use EDATE to move months forward or backward, and EOMONTH to find the end of a month. This enables simple timeline projections and due-date recalculations when dates drift.
=TODAY()+7 // date one week from today
=EDATE(DATE(2026,3,7), 3) // add 3 months
=EOMONTH(DATE(2026,3,7), 0) // end of March 2026Note: Month-based shifts take into account varying month lengths automatically.
Calculating date differences: DATEDIF and subtraction
Determining the interval between two dates is a common requirement. Subtracting two dates yields a number of days, while DATEDIF can return differences in days (D), months (M), or years (Y). When using DATEDIF, provide a start date, end date, and a unit code. If you need custom units, consider combining functions for precise outputs.
=A2 - B2 // difference in days
=DATEDIF(B2, A2, "D") // days between dates
=DATEDIF(B2, A2, "M") // full months between datesCaution: DATEDIF is not listed in all function menus, but it works reliably in Sheets. Always format the result as number if you plan to use it in dashboards.
Advanced date functions: EDATE, EOMONTH, and NETWORKDAYS
Beyond basic arithmetic, advanced date functions enable month-level shifts, end-of-month calculations, and workday counting. EDATE computes a date a given number of months away from a start date. EOMONTH returns the last day of a month. NETWORKDAYS counts weekdays between two dates, excluding weekends and optionally holidays. These functions empower robust planning workflows.
=EDATE(DATE(2026,1,15), 6) // 2026-07-15
=EOMONTH(DATE(2026,1,15), 0) // 2026-01-31
=NETWORKDAYS(DATE(2026,1,1), DATE(2026,1,31)) // 23 workdaysVariations: NETWORKDAYS.INTL lets you customize weekends, which is useful for non-standard work weeks.
Formatting dates, data hygiene, and common pitfalls
Date formatting is as important as the calculation itself. Always store raw dates as dates (not text) and apply a consistent date format for display. When importing data, validate that dates are recognized by Sheets, using ISDATE-like checks or value conversions. Mixing text dates with true date values is a frequent source of errors.
=IF(ISNUMBER(A2), A2, DATEVALUE(A2)) // normalize text to date where needed
=TEXT(DATE(2026,3,7), "yyyy-mm-dd") // consistent textual formatCommon pitfall: Treating a date as text will break arithmetic unless you convert it first.
Real-world patterns: date-driven dashboards and templates
A practical dashboard often relies on date logic for aging reports, due-date tracking, and milestone calendars. By combining TODAY(), DATEDIF, and EDATE, you can highlight overdue items, compute days until due, and project future milestones. Templates built around a date function foundation scale easily as data grows.
=IF(A2<TODAY(), "Overdue", "On Track")
=DATEDIF(TODAY(), A2, "D") // days until due
=EDATE(TODAY(), 1) // one month from todayBest practice: Keep a separate data sheet with raw date columns, and use a calculation sheet to present derived metrics.
Summary of best practices and debugging tips
When working with dates, keep these practices in mind: store dates as date values, format consistently, and test edge cases like month-ends and leap years. Use simple formulas to verify correctness (e.g., calculate a known date and compare to expected). If numbers look off, re-check that inputs are dates and not text, and ensure your locale settings align with date parsing.
=IF(A2<DATE(2026,1,1), "Old", "New")
=TEXT(A2, "ddd, mmm d, yyyy") // human-friendly displayAdvanced tip: Document your date logic clearly in comments or adjacent text cells to ease future maintenance.
Steps
Estimated time: 15-25 minutes
- 1
Plan date-driven goal
Identify the date-related goal for the sheet (e.g., due dates, milestones, or aging). Sketch the data columns and how dates will flow through calculations.
Tip: Write down exact date formats you will expect from data sources to avoid parsing errors. - 2
Create a date column with DATE
Use DATE(year, month, day) to construct fixed dates or to normalize input data into date values. Apply consistent formatting to display as required.
Tip: Test edge cases like month-end dates during data validation. - 3
Convert text to dates with DATEVALUE
When dates arrive as text, convert them using DATEVALUE and then perform arithmetic or comparisons.
Tip: Prefer ISO-formatted strings (YYYY-MM-DD) for better reliability. - 4
Perform date arithmetic
Add or subtract days, months, or years using simple arithmetic or functions like EDATE and EOMONTH.
Tip: Keep a separate cell area for base dates to prevent accidental overwrites. - 5
Validate results and build a dashboard
Audit your formulas with small test cases and assemble derived metrics (days until due, overdue counts, etc.).
Tip: Document assumptions and provide sample inputs/outputs in the sheet.
Prerequisites
Required
- Required
- Basic understanding of dates, numbers, and textRequired
- Required
Optional
- Optional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Insert current dateKeeps a static date in the selected cell | Ctrl+; |
| Insert current timeAdds time portion in the active cell | Ctrl+⇧+; |
| CopyCopy selected cell(s) | Ctrl+C |
| Paste values onlyPaste without formulas | Ctrl+⇧+V |
| Find in sheetNavigate search results | Ctrl+F |
FAQ
What is the difference between DATE and DATEVALUE in Google Sheets?
DATE creates a date from year, month, and day components. DATEVALUE converts a text string that looks like a date into a date value Sheets can compute with. If you have true date values, DATEVALUE is unnecessary; only convert when needed.
DATE builds a date from numbers, while DATEVALUE turns date-looking text into a date. Use DATEVALUE when your data comes as text.
How can I add months to a date in Sheets?
Use EDATE(startDate, months) to shift dates by whole months. For example, =EDATE(DATE(2026,3,15), 6) yields a date six months later. This handles month lengths automatically.
Use EDATE to move dates forward or backward by months, and it handles varying month lengths.
How do I count business days between two dates?
Use NETWORKDAYS(start_date, end_date) to count weekdays between two dates, excluding weekends. You can also specify holidays to exclude. This is useful for project timelines and delivery estimates.
NETWORKDAYS counts workdays between two dates and can exclude holidays.
What should I do if a date shows as 1900-01-00 in Sheets?
That usually indicates an invalid date input. Check your source data for text dates, blanks, or incorrectly parsed values, and normalize them using DATE or DATEVALUE before performing calculations.
If a date looks wrong, validate the input and convert where necessary.
Can I format dates to display as text automatically?
Yes. Use the TEXT function, e.g., =TEXT(date, "yyyy-MM-dd"), to produce consistent textual representations for dashboards and reports.
Format dates as text with TEXT(date, format) for consistent displays.
Is TODAY() updated when the sheet recalculates?
Yes. TODAY() returns the current date based on the spreadsheet's recalculation cycle and time zone settings.
TODAY updates to the current date when the sheet recalculates.
What’s the difference between DATE and year-month-day strings like 2026-03-07?
DATE creates a date from numbers; the string 2026-03-07 is parsed by DATEVALUE if written as text. When importing data, prefer explicit conversions to avoid locale-based misinterpretation.
Date values should be created with DATE or converted with DATEVALUE to ensure accuracy.
The Essentials
- Use DATE, TODAY, and EDATE for robust date calculations
- Convert text dates with DATEVALUE before arithmetic
- Leverage DATEDIF for age and duration calculations
- End-of-month and workday calculations help dashboards
- Validate and document date logic for maintainability