Excel date and time formulas turn plain numbers into readable schedules, reminders, and timestamps. Mastering these formulas helps you calculate deadlines, compare events, and automate reports without manual rework.
Use the table below to quickly identify the right formula for common date and time tasks, along with a simple example and key notes.
| Task | Formula Pattern | Example | Key Notes |
|---|---|---|---|
| Add days to a date | =A2 + days | =A2 + 30 | Positive for future, negative for past |
| Difference between two dates | =end_date - start_date | =B2 - A2 | Returns total days as a number |
| Extract current date | =TODAY() | =TODAY() | Volatile, updates on recalculation |
| Extract current time | =NOW() | =NOW() | Includes date and time, volatile |
| Format time parts | =HOUR(timestamp), MINUTE(timestamp), SECOND(timestamp) | =HOUR(C2) | Returns hour, minute, or second as number |
| Build a date from components | =DATE(year, month, day) | =DATE(2024, 12, 25) | Returns a valid Excel date serial |
| Combine date and time | =A2 + B2 | =A2 + B2 | Date in A2, time in B2, serial sum |
| Text to date conversion | =DATEVALUE(text) | =DATEVALUE("2024-06-15") | Result may need formatting |
How Excel Stores Dates And Times Internally
Excel uses a serial number system where dates are integers and times are fractions of a day. Understanding this model helps you avoid surprises when formulas return unexpected numbers or errors.
For example, 1-Jan-1900 equals serial number 1, and 0.5 represents 12:00 PM on that date. Dates stored as integers make it easy to add or subtract days using simple arithmetic.
Common Date Functions For Calculations
TODAY And NOW For Real Time Stamps
TODAY() returns the current date without time, while NOW() returns both date and time. Both are volatile, meaning they update each time the worksheet recalculates, which is ideal for logs and freshness checks.
DATE And Time Part Functions
Use DATE(year, month, day) to build a valid date from separate components, ensuring Excel recognizes it as a proper serial number. Functions like YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), and SECOND() let you pull out individual parts for reporting or conditional logic.
Date Arithmetic And Duration Calculations
Adding And Subtracting Days
Adding or subtracting integers to dates shifts them forward or backward by whole days. For example, =A2 + 7 gives a date one week later, and =A2 - 1 gives the previous day.
Calculating Differences In Days, Weeks, And Months
Simple subtraction returns the difference in days. To get weeks, divide by 7 and use ROUNDDOWN or QUOTIENT. For months, combine YEAR and MONTH or use DATEDIF for whole month differences across years.
Formatting And Error Handling Best Practices
Apply Date And Time Formats
Use custom formats like yyyy-mm-dd hh:mm to control display without changing values. Always verify cell formats when results look wrong, because Excel often shows dates as numbers if formatting is missing.
Avoid Common Pitfalls
Watch for text dates that behave like numbers only after conversion, leap year effects on day differences, and system time zone influences on NOW(). Use ISNUMBER and error traps to keep your date and time Excel formula logic stable.
Key Takeaways For Efficient Date And Time Workflows
- Remember that dates are integers and times are fractions in Excel’s serial system
- Use TODAY() and NOW() for dynamic stamps, DATE() to build reliable date values
- Perform arithmetic with simple加减 to manage schedules and durations
- Format cells consistently and handle text dates with DATEVALUE or parsing
- Leverage WORKDAY and DATEDIF for business days and month-level differences
FAQ
Reader questions
How do I calculate workdays excluding weekends using date formulas?
Use WORKDAY(start_date, days) to move forward or backward by business days, automatically skipping weekends. For custom holiday lists, use WORKDAY.INTL with a range specifying nonworking days.
Can I compare two date columns to flag differences in days?
Yes, subtract the two date columns to get the day difference, then use IF or conditional formatting to highlight mismatches. Format the result column as General or Number to see clean numeric differences.
Why does my date formula return a large number instead of a calendar date?
This happens when the cell format is General or Number. Apply a date format such as yyyy-mm-dd so Excel displays a recognizable date while the underlying serial value stays correct.
How can I convert text like '2024-07-01' into a real Excel date?
Use DATEVALUE to convert ISO text into a date serial, then apply a date format. If DATEVALUE returns an error, check regional settings or use VALUE and DATE functions with LEFT, MID, RIGHT to parse components manually.