What is the NOW Function?

The NOW function returns the current date and time, updating automatically when the spreadsheet recalculates. Perfect for timestamps and real-time tracking.

How to fix #N/A error in NOW Function?

The #N/A error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #REF! error in NOW Function?

The #REF! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #VALUE! error in NOW Function?

The #VALUE! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #DIV/0! error in NOW Function?

The #DIV/0! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #NAME? error in NOW Function?

The #NAME? error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

NOW Function

Quick Answer

Comprehensive Explanation

Syntax and Parameters

How to Use NOW - Step by Step

Practical Examples

Basic Timestamp Creation

Add a timestamp showing when data was entered

Result: 1/15/2025 14:30:45

Calculate Age in Years

Calculate someone's exact age using their birthdate

Result: 32

Time Since Last Update

Show elapsed time since a specific timestamp

Result: 2.45 days

Countdown Timer to Deadline

Create a countdown showing time remaining until a deadline

Result: 5 days 14 hours 32 minutes

Working Hours Calculation

Check if current time is within business hours

Result: Open

Auto-Refresh Dashboard Header

Display last refresh time in a dashboard

Result: Dashboard last updated: Jan 15, 2025 at 2:30 PM

Common Errors and Solutions

#NAME?

Excel doesn't recognize the function name

Cause:

Missing parentheses - typing =NOW instead of =NOW()

Solution:

Always include empty parentheses: =NOW(). Even though NOW takes no arguments, the parentheses are mandatory for Excel to recognize it as a function.

Prevention:

Remember that all Excel functions require parentheses, even when no arguments are needed

Frequency: 35%

Example:

Static Value

NOW() shows old time and doesn't update

Cause:

Calculation mode set to Manual or value was converted to static

Solution:

1. Check calculation mode: Formulas tab → Calculation Options → Automatic 2. Press F9 to force recalculation 3. In Google Sheets, make any edit to trigger update 4. Check if cell was paste-special'd as values

Prevention:

Keep automatic calculation enabled for real-time updates

Frequency: 25%

Wrong Format

Shows number like 45672.625 instead of date/time

Cause:

Cell formatted as Number or General instead of Date/Time

Solution:

Right-click cell → Format Cells → Date or Time category → Choose desired format. The serial number is correct, just needs proper formatting.

Prevention:

Pre-format cells as Date/Time before entering NOW()

Frequency: 20%

Example:

Performance Issues

Spreadsheet becomes slow with multiple NOW() functions

Cause:

NOW() is volatile and recalculates frequently, impacting performance

Solution:

1. Limit NOW() usage - use one cell and reference it elsewhere 2. Convert to static values where real-time updates aren't needed 3. Use TODAY() if time component isn't required 4. Consider VBA/Apps Script for controlled updates

Prevention:

Design spreadsheets with minimal volatile functions

Frequency: 15%

Time Zone Issues

NOW() shows wrong time compared to expected

Cause:

Function returns system time, which may differ from user's expected time zone

Solution:

1. Adjust system time zone settings 2. Add/subtract hours for time zone conversion: =NOW()±(hours/24) 3. Use TIME function to adjust: =NOW()+TIME(offset,0,0)

Prevention:

Document which time zone the spreadsheet uses

Frequency: 5%

Example:

Advanced Tips and Techniques

Use keyboard shortcuts for non-updating timestamps: • Ctrl+; (semicolon) = Current date only • Ctrl+Shift+; = Current time only • Ctrl+; [space] Ctrl+Shift+; = Date and time These create static values that won't change.

For better performance with NOW(): 1. Use a single NOW() cell and reference it elsewhere (=$A$1) 2. Turn off automatic calculation for large models 3. Use TODAY() when time isn't needed 4. Consider storing timestamps as values after entry

NOW() recalculates with every spreadsheet change, which can slow down large workbooks. Each NOW() instance adds to calculation time. Monitor performance and consider alternatives for static timestamps.

Useful NOW() format codes: • "yyyy-mm-dd hh:mm:ss" - ISO format • "dddd, mmmm d, yyyy" - Friday, January 15, 2025 • "mm/dd hh:mm AM/PM" - 01/15 02:30 PM • "[h]:mm:ss" - Total hours elapsed • "d \"days\" h \"hours\"" - Elapsed time format

When using NOW() for timestamps, always document: 1. The time zone being used 2. Whether updates are automatic or manual 3. The purpose of the timestamp 4. How to convert to static if needed This prevents confusion in shared workbooks.

Key differences between platforms: • Excel: Updates on F9 or cell changes • Google Sheets: Auto-updates every minute • Excel Online: May have slight delays • Numbers (Mac): Updates on any interaction • LibreOffice: Configurable update interval

Related Formulas and Alternatives

NOW Function

Practical Examples

Basic Timestamp Creation

Calculate Age in Years

Time Since Last Update

Countdown Timer to Deadline

Working Hours Calculation

Auto-Refresh Dashboard Header

Common Errors and Solutions

Advanced Tips and Techniques

Need Help with NOW Function?

Related Formulas