Date and Time Functions in Excel: TODAY, NOW & DATEDIF Explained

In this tutorial, we’ll explore three of the most powerful — and beginner-friendly — date and time functions in Excel: TODAY(), NOW(), and DATEDIF(). By the end, you’ll be using them like a pro in your spreadsheets.

Understanding Excel’s Date and Time System

Before we jump into the fun part, you should know that Excel doesn’t actually store dates and times as you see them. Behind the scenes, every date is just a serial number.

• 🗓️ Dates are whole numbers — for example, January 1, 1900 is stored as 1.
• ⏰ Times are decimal fractions — where 0.5 means noon (half a day).

This might sound geeky, but it’s actually brilliant because it allows Excel to perform date arithmetic easily — like finding how many days until your next vacation. 🎉

1. The TODAY() Function

What It Does

TODAY() returns the current date based on your system clock. The beauty of this function is that it updates automatically every day when you open the workbook.

Syntax

=TODAY()

Yes, that’s it. No arguments, no drama — just pure date magic.

Example 1: Display Today’s Date

If you want to display today’s date in cell A1, simply type:

=TODAY()

Excel will return something like 11-Oct-2025 (depending on your region and system date).

Example 2: Calculate a Future Date

Say you have a task due in 10 days. You can calculate the due date like this:

=TODAY() + 10

Excel adds 10 days to today’s date and gives you the new deadline.

Example 3: Find Days Left Until an Event

Want to count down to a birthday or event on B1? Use this:

=B1 - TODAY()

Result: Number of days remaining. 🎂

2. The NOW() Function

What It Does

The NOW() function is like TODAY’s cooler cousin — it gives you the current date and time.

Syntax

=NOW()

Example 1: Display Current Date and Time

Type this into a cell:

=NOW()

Excel will show something like 11-Oct-2025 07:45 PM (again, based on your system clock).

Example 2: Calculate Elapsed Time

If you’re tracking how many hours have passed since a project started (say the start time is in cell A1):

=NOW() - A1

Format the result as time, and voilà — you’ll see how long it’s been since the project kicked off.

Example 3: Timestamping with NOW()

Want to record the exact time of an entry? Simply type =NOW() — but remember, it updates dynamically. If you want to “freeze” the timestamp, copy the cell and use Paste → Values to keep it static.

Pro Tip: The NOW function updates whenever your worksheet recalculates, so be mindful if you’re working with time-sensitive data.

3. The DATEDIF() Function

What It Does

DATEDIF() is one of Excel’s hidden gems — it calculates the difference between two dates in various units (days, months, or years). This function isn’t listed in Excel’s formula suggestions, but trust me, it’s powerful!

Syntax

=DATEDIF(start_date, end_date, unit)

• start_date: The beginning date.
• end_date: The ending date.
• unit: The type of result you want — "D" (days), "M" (months), or "Y" (years).

Example 1: Calculate Someone’s Age

Let’s say someone’s birth date is in A1 and today’s date is… well, TODAY().

=DATEDIF(A1, TODAY(), "Y")

Excel returns the person’s age in full years. 🎉

Example 2: Find How Many Months Have Passed

To get the number of complete months between two dates:

=DATEDIF(A1, B1, "M")

Example 3: Get the Total Days Difference

To find the total number of days between two dates:

=DATEDIF(A1, B1, "D")

Bonus: Combine Units

You can even combine multiple DATEDIF functions for a more readable result. For example:

=DATEDIF(A1, TODAY(), "Y") & " years, " & DATEDIF(A1, TODAY(), "YM") & " months"

This displays something like: 25 years, 4 months.

Formatting Tips for Dates and Times

• Use Ctrl + 1 (Windows) or Cmd + 1 (Mac) to open the Format Cells dialog.
• Choose “Date” or “Time” for quick presets.
• Use custom formats like dd-mmm-yyyy for dates and hh:mm AM/PM for time.

Example: If a cell shows 45321.625, formatting it as date/time might display 10-Oct-2024 3:00 PM. That’s Excel magic — turning serial numbers into readable timestamps.

Common Mistakes to Avoid

• ❌ Forgetting the quotation marks around the DATEDIF unit (e.g., "Y", "M", or "D").
• ❌ Using DATEDIF with reversed start and end dates — it can produce errors.
• ❌ Confusing TODAY() and NOW() — one gives just the date, the other includes time.
• ✅ Always check your date format (regional settings can affect results).

Conclusion

And that’s a wrap! 🎉 You’ve just learned how to use three of Excel’s most practical and time-saving functions — TODAY(), NOW(), and DATEDIF(). These functions are your best friends when dealing with deadlines, schedules, project timelines, or age calculations.

Next time you open Excel, try this challenge: Create a mini age calculator using DATEDIF, or a countdown to your next big event using TODAY. Trust me — once you start playing with these, you’ll wonder how you ever managed dates manually!