Excel: NOW, or TODAY?

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: Why did you use TODAY() in the previous topic. Isn't this the same as NOW()?

Strategy: The NOW function will return the date and time that the workbook was last calculated. Workbooks are calculated when they are opened, when you enter a value in the worksheet, or when you press the F9 key. In other words, they are calculated a lot.

If you enter NOW in a cell, it will generally show the current date and a fairly recent time.

The TODAY function is similar to NOW, except it returns only the current date. In many cases, the TODAY function is more appropriate for calculating the number of days between today and a deadline.

Below, cell B1 contains a due date. If you calculate =B1-NOW(), Excel will say that it is 12.561 days away. If you calculate =B1-TODAY(), Excel will say that it is 13 days away. If you go into work on Monday, then most people would say that Wednesday is 2 days away. If you use NOW instead of TODAY then at 9 a.m., Excel would say that Wednesday is 1.625 days away.

  1. For whole days, use TODAY instead of NOW.

    Additional Details: To calculate the current time, you could use =NOW()-TODAY() or =MOD(NOW(),1). Make sure to format the resulting cell as a time.