Excel: Use NOW Function to Return Date Only

When working with time-sensitive data in Microsoft Excel, users frequently need to record the current date. The platform provides several built-in functions to handle this requirement, most notably the NOW function and the TODAY function. While the TODAY function is straightforward, returning only the current date, the NOW function is slightly more complex, returning both the current date and the precise time.

For scenarios where the time component is unnecessary, displaying the full output of the NOW function can clutter your worksheet. Fortunately, there are effective methods to manipulate the NOW function’s output to extract only the date, ensuring your data remains clean and focused. This detailed guide explores how to achieve this extraction, contrasting it with the easier TODAY alternative.

The Default Behavior of the NOW Function

By default, the NOW function calculates and returns the current date and time. It is important to understand that Excel stores dates and times as sequential serial numbers, where the integer part represents the date (the number of days since January 1, 1900) and the fractional decimal part represents the time (a fraction of a 24-hour day). For instance, a return value of 45200.5 signifies the date represented by day 45200, occurring at noon (0.5 of a day).

To use the standard NOW function and observe its default output, one simply enters the formula:

=NOW()

This formula is dynamic; it recalculates whenever the spreadsheet is opened or when any cell value changes, ensuring the returned date and time are always current relative to the system clock. When the goal is strictly the date, we must employ a mathematical technique to discard the fractional time component.

Method 1: Extracting Date Only Using the INT Function

Since the date is stored as the integer portion of Excel’s serial number system, we can utilize the INT function—which stands for Integer—to truncate the time component. The INT function rounds a number down to the nearest integer, effectively removing any decimal fraction attached to the number.

When the output of the NOW function (Date + Time) is passed into the INT function, the result is the same serial number, but without the decimal component representing the time. This leaves us with the exact date as an integer value. The specific syntax required to accomplish this is:

=INT(NOW())

While this formula successfully returns the date, it is often necessary to apply formatting to ensure the result is displayed in a recognizable date format rather than a raw serial number.

Step-by-Step Example: Implementing INT(NOW()) in Practice

Let’s walk through a practical demonstration of using the NOW function and then manipulating its output to show only the date. We begin by entering the basic NOW formula into a target cell, such as A1, to see the default output:

=NOW()

The screenshot below illustrates the result of the standard NOW function, clearly showing both the date and time components based on the system clock at the time of calculation:

Next, we modify the formula in cell A1 to integrate the INT function, thereby instructing Excel to return only the integer portion of the current serial date value:

=INT(NOW())

The resulting output after applying INT(NOW()) might initially appear as a raw serial number or potentially still show a time component depending on Excel’s default formatting. However, the underlying value is now correctly truncated to represent only the date, as shown here:

Formatting the Output: Applying the Short Date Style

Once the INT(NOW()) formula has been implemented, the returned value is technically the date, but it needs proper visual styling. If the cell is not automatically formatted as a date, it might display a general number format (e.g., 45200) or an ambiguous date/time format.

To ensure clarity, we must explicitly apply the Short Date format. This involves navigating to the ribbon controls. With the output cell (e.g., A1) selected, follow these steps:

• Locate the Home tab along the top ribbon.
• Find the Number group within the Home tab.
• Click the Number Format dropdown menu (usually displaying “General”).
• Select Short Date from the list of options.

The following illustration highlights the selection process for the Short Date format:

After applying this formatting, cell A1 will now display the accurate, dynamic current date, successfully isolating the date component from the original NOW function’s output:

Method 2: Leveraging the Simplicity of the TODAY Function

While manipulating the NOW function using INT is a valuable technical exercise, Excel provides a much simpler, dedicated function for retrieving the current date without the time component: the TODAY function. The TODAY function inherently calculates only the integer part of the current serial date number, eliminating the need for additional nested functions like INT.

To use this direct approach, simply input the following formula into your desired cell:

=TODAY()

Like the NOW function, the TODAY function is volatile, meaning it updates automatically upon recalculation or workbook opening. This ensures the date always reflects the current system date. The output using this method is identical in value and typical display format to the result of INT(NOW()), but achieved with greater efficiency.

The following screenshot demonstrates the clean output generated by the TODAY function:

Comparison and Best Practices

We have successfully demonstrated two distinct methods for retrieving the current date in Excel: using the complex but instructive INT(NOW()), and using the simple TODAY(). Both methods return the exact same date value, matching the integer serial number of the current day.

The choice between these two methods depends primarily on context and intent:

• Use INT(NOW()): This approach is useful when you are already working with a data set that includes date and time stamps (perhaps retrieved from a system import) and you need to strip the time component for comparison or categorization. It provides a deeper understanding of how Excel handles serial date calculations.
• Use TODAY(): This is the recommended, industry-standard best practice when the only requirement is to display the current, dynamic date. It is shorter, clearer, and requires less processing overhead, making the formula easier for other users to read and maintain.

Ultimately, unless there is a specific technical reason to use the NOW function and truncate it, utilizing the dedicated TODAY function is the superior method for displaying the current date only.