How to Use the NOW Function in Excel

Microsoft Excel provides a wide range of date and time functions that help users automate calculations, track events, and create dynamic spreadsheets. Among these, the NOW function stands out as one of the most useful and frequently used functions when working with current date and time values.

Whether you are building dashboards, tracking task updates, logging timestamps, or creating time-sensitive formulas, understanding how the NOW function works is essential. This article provides a comprehensive explanation of the NOW function in Excel, including its syntax, practical examples, common use cases, formatting tips, limitations, and best practices.

What Is the NOW Function in Excel?

The NOW function is a built-in Excel date and time function that returns the current date and current time based on your system’s clock. Unlike static values, the result of the NOW function updates automatically whenever the worksheet recalculates.

In simple terms:

• NOW() gives you today’s date + the current time
• The value updates whenever Excel recalculates the workbook

This makes the NOW function ideal for situations where you need real-time or near-real-time timestamps.

Syntax of the NOW Function

The syntax of the NOW function is very simple:

=NOW()

Key Points About the Syntax

• The NOW function takes no arguments
• Parentheses are required, even though nothing is inside them
• The function always returns a serial number representing date and time

Understanding How Excel Stores Date and Time

To fully understand the NOW function, it helps to know how Excel handles dates and times internally.

Date Serial Numbers

Excel stores dates as sequential serial numbers:

• January 1, 1900 = 1
• January 2, 1900 = 2
• And so on…

Time as a Fraction

Time is stored as a decimal fraction of a day:

• 12:00 PM = 0.5
• 6:00 AM = 0.25
• 6:00 PM = 0.75

When you use the NOW function, Excel combines both:

Date (integer) + Time (decimal)

This is why NOW() can be used in calculations involving time differences and durations.

Basic Example of the NOW Function

To use the NOW function:

1. Select a cell

2. Enter the formula:

   =NOW()
   

3. Press Enter

Excel will display the current date and time, for example:

14/01/2026 17:42

(The format depends on your regional settings.)

Formatting the NOW Function Output

By default, Excel may not display the result in your preferred format. You can easily customize how the date and time appear.

How to Format NOW() Output

1. Select the cell containing =NOW()
2. Press Ctrl + 1 (or right-click → Format Cells)
3. Choose Date, Time, or Custom
4. Select or define a format

Common Custom Formats

Formatting does not change the underlying value—only how it is displayed.

Difference Between NOW and TODAY Functions

Excel has two similar functions that are often confused: NOW and TODAY.

Example Comparison

• =TODAY() → 14/01/2026
• =NOW() → 14/01/2026 17:42

Use NOW when time matters, and TODAY when you only need the date.

Common Use Cases for the NOW Function

1. Timestamping Data Entries

The NOW function is frequently used to record when data is entered or updated.

Example:

=NOW()

This is useful for:

• Logging form submissions
• Tracking updates in project sheets
• Recording last modified times

⚠️ Note: Because NOW is volatile, it updates on recalculation. For permanent timestamps, VBA or manual entry is required.

2. Calculating Time Differences

You can subtract two date-time values to calculate elapsed time.

Example:

=NOW() - A1

Where A1 contains a past date and time.

To convert the result:

• Hours: =(NOW()-A1)*24
• Minutes: =(NOW()-A1)*1440
• Seconds: =(NOW()-A1)*86400

3. Countdown or Deadline Tracking

You can calculate remaining time until a deadline.

Example:

=A1 - NOW()

Where A1 is a future date and time.

This is commonly used in:

• Task deadlines
• Event countdowns
• SLA tracking

4. Conditional Formatting with NOW

You can combine NOW with conditional formatting to highlight overdue items.

Example formula:

=A1 < NOW()

Use this to:

• Highlight expired deadlines
• Flag overdue tasks
• Identify missed appointments

5. Calculating Working Hours or Shift Durations

When paired with start times, NOW can calculate live working durations.

Example:

=NOW() - Start_Time

Formatted as [h]:mm:ss to display continuous hours.

Using NOW with Other Excel Functions

NOW with IF Function

Example:

=IF(NOW()>A1,"Expired","Active")

This checks whether the current date and time have passed a specific deadline.

NOW with TEXT Function

Convert NOW into a text string:

=TEXT(NOW(),"yyyy-mm-dd hh:mm:ss")

This is useful when:

• Exporting data
• Creating logs
• Displaying formatted timestamps in text fields

NOW with NETWORKDAYS or WORKDAY

While NOW includes time, you can combine it with workday calculations by extracting the date:

=WORKDAY(INT(NOW()),5)

This calculates a future business date based on today.

Volatile Nature of the NOW Function

One of the most important characteristics of the NOW function is that it is volatile.

What Does Volatile Mean?

A volatile function recalculates whenever:

• A cell changes
• The worksheet recalculates
• The workbook is opened
• You press F9

This means:

• The value of NOW() constantly changes
• It is not suitable for fixed timestamps

How to Freeze the NOW Function Value

If you want a static date and time:

Method 1: Copy and Paste Values

1. Enter =NOW()
2. Copy the cell
3. Paste as Values

Method 2: Keyboard Shortcut

• Ctrl + ; → Inserts current date
• Ctrl + Shift + ; → Inserts current time

These shortcuts insert fixed values, not formulas.

Common Errors and Mistakes

1. Expecting a Fixed Timestamp

Many users assume NOW() records a permanent time. It does not.

2. Incorrect Formatting

Seeing numbers like 45234.742 means the cell is not formatted correctly.

3. Performance Issues in Large Files

Using NOW in thousands of cells can slow down large spreadsheets due to recalculation.

Best Practices for Using the NOW Function

• Use NOW only when dynamic time updates are required
• Avoid using NOW in large datasets unless necessary
• Combine NOW with formatting for readability
• Use static timestamps when tracking historical records
• Document formulas that rely on volatile functions

Practical Example: Task Deadline Tracker

Imagine a task list with deadlines in column B.

Formula in column C:

=B2 - NOW()

Formatted as:

[d] "days" hh:mm

This creates a live countdown for each task, updating automatically.

Conclusion

The NOW function is a powerful and versatile Excel tool for working with real-time date and time values. Its simplicity makes it easy to use, while its dynamic nature allows you to build responsive spreadsheets that adapt automatically as time passes.

From calculating durations and tracking deadlines to generating live timestamps and conditional alerts, the NOW function plays a crucial role in many professional Excel workflows. However, understanding its volatile behavior and limitations is essential to using it effectively.

By mastering the NOW function and combining it with other Excel features, you can create smarter, more time-aware spreadsheets that improve accuracy, automation, and efficiency.