Google Sheet Today: A Practical Guide to Date Formulas
Master TODAY() and date formulas in Google Sheets with practical, step-by-step guidance for students, professionals, and small businesses. Build dynamic dashboards, stamp dates, and automate date-based logic confidently.
Use TODAY() to pull the google sheet today date. The TODAY function returns the current date in Google Sheets and updates daily. It powers dynamic dashboards, date stamping, and date-based logic when combined with TEXT, IF, and NETWORKDAYS. How To Sheets analyzes patterns and gotchas to help you build reliable date logic. Whether you're a student tracking deadlines or a professional reporting timelines, TODAY() unlocks templates that stay current.
TODAY() basics: getting the current date and formatting
The TODAY() function is the simplest way to insert the current date into a Google Sheets cell. It updates automatically when the sheet recalculates, making it ideal for date stamps and time-sensitive dashboards. The value returned is a date serial number that your sheet can format in many ways. If you only need the date in a readable form, pair TODAY() with TEXT to control the display format. The combined use is common in templates where you want both a live date and a consistent appearance.
=TODAY()=TEXT(TODAY(),"yyyy-MM-dd")Why it matters: using TODAY() keeps templates lightweight and keeps deadlines up to date. In multi-user sheets, the date reflects each user’s locale and timezone, which can affect formatting and comparisons. For stable dates in reports, consider freezing values when needed and documenting the intended behavior. The How To Sheets team highlights these patterns to prevent common gotchas in date logic.
Dynamic date usage in workflows
Dynamic dates enable automation and smarter decision rules. In practice, you often compare a date column against TODAY() to trigger statuses or alerts. Below are common patterns you’ll likely reuse:
=IF(A2<=TODAY()+7,"Due soon","Done")=NETWORKDAYS(TODAY(), B2)Line-by-line:
- The first formula flags items due within the next week; if the date in A2 is before or within seven days from today, it returns "Due soon". Otherwise, it returns "Done". This is perfect for task trackers or reminders.
- The second formula counts working days from today to a due date in B2, ignoring weekends by default. It’s ideal for project schedules and SLA calculations.
Alternatives: you can substitute TODAY() with NOW() when you also need the current time, but note NOW() has a time component that can affect comparisons and formatting.
Static vs dynamic dates and immutability
A date produced by TODAY() is dynamic and can shift as days pass. In templates that require a fixed timestamp, you must convert the dynamic value to a static one. Several approaches exist:
function freezeTodayInRange() {
var sheet = SpreadsheetApp.getActiveSheet();
var today = new Date();
sheet.getRange("D2").setValue(today);
}This Apps Script snippet writes the current date into D2 as a fixed value. After running, the cell will no longer auto-update unless you explicitly refresh it. If you’re not using Apps Script, you can manually copy the TODAY() cell and use Paste special > Paste values only to freeze the date in place.
When to freeze: use static dates for historical records, snapshots for auditing, or when exporting data to external systems that don’t recalculate on their own.
Practical variations: NOW(), EDATE, EOMONTH
TODAY() gives the date, but many workflows need the current date plus time or date arithmetic. Here are useful variations:
=NOW() // includes date and time=EDATE(TODAY(), 3) // date three months from todayUse cases: NOW() is great for time-stamped entries; EDATE() helps with monthly anniversaries or lease dates. EOMONTH(TODAY(), 0) returns the end of the current month, which is useful for monthly reporting and budget cycles. When combining these with TEXT, you can present consistent formats across dashboards.
Common pitfalls and debugging date formulas
Date formulas can fail for locale and formatting reasons. Common pitfalls include mismatched date formats and using TEXT in ways that break calculations:
=TEXT(TODAY(),"MM/dd/yyyy") // ok in many locales=TODAY() + "1" // Type mismatch, returns #VALUE!Another frequent issue is assuming a date string is numeric. In Sheets, DATE(year, month, day) is more reliable for construction; otherwise, ensure inputs are proper date values before arithmetic. When debugging, break complex formulas into smaller parts and verify each intermediate result with a simple =A1 formula.
Real-world template integration: project tracker
Date formulas underpin many templates like project trackers and attendance sheets. Practical examples:
=IF(TODAY()=C2, "Due today", "")=IF(A2 < TODAY(), "Overdue", "On track")Scenario: In a task list, column C contains due dates. The first formula marks tasks due today, enabling conditional formatting or notifications. The second flags overdue items. Combining such checks with conditional formatting makes dates immediately actionable in shared templates. For better UX, keep a single source of truth for the date logic and document expected behaviors for collaborators.
Tips and optimization strategies
Date logic scales with data size. Here are a few starter tips:
=IFERROR(DATEVALUE(TEXT(TODAY(),"yyyy-mm-dd")), TODAY())ELSE: Use named ranges for date columns to simplify formulas and reduce errors when rows are added or removed.
**Performance note:** Complex date calculations in large sheets can slow down recalculation. Favor simple, modular formulas and avoid chaining volatile functions more than necessary. Document assumptions about time zones and locale to help teammates understand the behavior across devices.
Quick-start ideas and templates
Kick off with a minimal, date-driven template and gradually expand. A simple project board might include:
A2: Task
B2: Due date
C2: Status
=D2: TODAY() // helper to show the current date in a headerAs you add tasks, use TODAY() to compute status or overdue indicators. You can then layer in conditional formatting rules to highlight urgency and generate weekly reports with a single formula. This approach keeps templates lightweight while staying responsive to daily changes.
Steps
Estimated time: 60-90 minutes
- 1
Identify date use-cases
List where dates impact decisions in your workbook: dashboards, due dates, reminders, and reporting periods. Define the required formats and any locale considerations.
Tip: Document assumptions about time zones and date formats before building formulas. - 2
Create a sample sheet
Set up a small sheet with a date column and a few tasks. Include a column for due dates and a status column to test conditional logic.
Tip: Use named ranges for key date columns to simplify maintenance. - 3
Enter TODAY() formulas and format
Insert TODAY() in a header cell and format it with TEXT to display as YYYY-MM-DD for consistency across reports.
Tip: Store the formatted string in a separate column if you need both raw date and display text. - 4
Add date-based logic
Create rules like due-today, overdue, and upcoming using TODAY() in IF statements and NETWORKDAYS for working days.
Tip: Test with sample dates to validate edge cases around weekends and holidays. - 5
Freeze a snapshot date (optional)
If you need a fixed date, copy the TODAY() result and paste values. You can also use Apps Script to write the current date into a target cell.
Tip: Document where snapshots are used and why they aren’t dynamic. - 6
Validate and share
Check calculations against a controlled dataset, then share with teammates. Add notes explaining time-zone behavior and formatting decisions.
Tip: Encourage teammates to review date logic for consistency.
Prerequisites
Required
- Required
- Google Sheets in a modern browserRequired
- Basic knowledge of formulas (SUM, IF)Required
Optional
- Optional
- Familiarity with date formats and localesOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cell or range | Ctrl+C |
| PastePaste values or formulas depending on context | Ctrl+V |
| Fill downFill formulas downward | Ctrl+D |
| UndoUndo last action | Ctrl+Z |
| FindFind within sheet | Ctrl+F |
| Find and replaceGlobal search and replace | Ctrl+H |
FAQ
What does TODAY() return in Google Sheets?
TODAY() returns the current date according to the sheet's locale. It updates automatically when the sheet recalculates, but it does not include a time component. Use NOW() if you also need the current time.
TODAY() gives the current date and updates every time the sheet recalculates; it does not include time.
How can I format TODAY() to a specific date format?
Use the TEXT function to control the display format, e.g., =TEXT(TODAY(), 'yyyy-MM-dd'). This keeps a readable date while preserving the underlying serial date for math.
Format TODAY() with TEXT to show the date in your preferred layout.
How do I freeze today's date so it doesn't change?
TODAY() is inherently dynamic. To freeze a date, copy the cell and Paste special > Paste values only, or use Apps Script to write the date to a fixed cell.
Copy the date and paste values to freeze it, or use a small script to write the date.
Can I use TODAY() in conditional formatting?
Yes. You can reference TODAY() in conditional formatting rules to highlight past due dates, due today, or upcoming deadlines. Combine with relative ranges for scalable templates.
Absolutely—TODAY() works well in conditional formatting to alert you on date-driven conditions.
What is the difference between TODAY() and NOW()?
TODAY() returns just the date, while NOW() returns the current date and time. Use NOW() when timestamps with time matter; otherwise, TODAY() keeps formulas simpler and more stable for date-only logic.
TODAY() gives date only; NOW() adds the current time.
How can I handle time zones in date calculations?
Google Sheets uses your account and browser settings for date interpretation. For cross-time-zone templates, document expected behavior and, if needed, adjust with date-time functions and locale-aware formatting.
Time zones matter; document assumptions and adjust with relevant date-time functions.
The Essentials
- Use TODAY() for dynamic date stamping in Sheets
- Format dates with TEXT to ensure consistent display
- Combine TODAY() with IF, NETWORKDAYS, and EDATE for date-based automation
- Know when to freeze dates with paste-values or Apps Script
- Document locale and time-zone assumptions for shareable templates