Excel functions: Why Today() is better than Now()

Posted by David H Ringstrom PM | on Thu, 10/05/2012 - 15:19 4063 11 comments

Excel lecturer David Ringstrom continues to share his productivity tips with the trans-Atlantic Excel user community.

Whey they are building date-related formulae, many Excel users apply the NOW() function to return today;s date in their worksheets. It works, but in some situations NOW() can cause a formula headache.

The NOW() function returns the day’s date along with the current time in Excel’s digital date format. So if it’s noon on 21 December 2012, the =NOW()formula would return the result 41,264.50 in your worksheet cell. This is because 21/12/12 is 41,264 days after 1 January 1900, the “zero day” from which numerical dates are calculated in Excel.

The .50 element of the result represents half of a twenty-four-hour day. Thus, if your formula compares a static date value, such as 12/21/12 to the NOW() function function result, Excel will show a mismatch, because 41,264 does not equal to 41,264.50.

So the more efficient way to compares is to use the TODAY() function instead, since it returns the date portion only: 41,264 in the case of our example date 21/12/12.

Further reading
David Ringstrom's Excel tutorials on AccountingWEB.co.uk
ExcelZone Compendium 2007
Excel navigation tips
ExcelZone - a huge archive of tips and tutorials. Be sure to visit the subscriptions page in MyAccountingWEB to up for our monthly ExcelZone bulletin.

About the author
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com.

Today() 1 thanks

ronplan | Fri, 11/05/2012 - 17:29 | Permalink

Much better. I have been using the following to get around the problem:

=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

Thanks

Confused

brian.barrett | Sun, 13/05/2012 - 11:41 | Permalink

If you wish to stay with the NOW() function why not use:

=INT(NOW()) ?

That's great 1 thanks

richardpoulter | Thu, 16/08/2012 - 12:23 | Permalink

That's cleared up the mystery of why comparing the result of a built-in function which evidently returns a 2-part compound value, with a value consisting of just one part, results in a mismatch (except at the stroke of midnight). Well I never :-p

Apologies ronplan

brian.barrett | Mon, 14/05/2012 - 03:10 | Permalink

I originally believed you were suggesting that it was ...

... much better to use the formula, =DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

I have re-read and understand!

Int

ronplan | Mon, 14/05/2012 - 17:24 | Permalink

Apologies accepted. I could have said it better.

Good idea on the "Int". I could also have used =TRUNC((NOW)).

Sometimes I just carry some old baggage around from when it used to be harder. Then again, I may just be getter foggier with age (is that where "old fogie" comes from?).

Enlightened 1 thanks

sedakensearch | Fri, 08/06/2012 - 14:49 | Permalink

never thought why today() returned 41068 now i know,

just found out i have lived 12,238 days in this planet

I would have thought the

Nuncle | Thu, 16/08/2012 - 14:28 | Permalink

I would have thought the cross section of Excel users who are competant enough to use the NOW() function within formulae, but didn't know how Excel handles and stores date values would be quite slim.

Good reason to look through the whole function list 1 thanks

duncanphilpstate | Fri, 17/08/2012 - 09:59 | Permalink

Every now and then - usually while writing a formula if not under presure (huh!) - I like to scan the function library from the formula bar just to see if there is anything I've missed or that looks useful now or in future. That way you stand chance of spotting that there is both TODAY() and NOW() and giving yourself the choice.

I'm continually puzzled by the numbers of people - even working as accountants - who are very limited in their use of Excel (and I'm not talking about writing macros here) to the point where at best they make more work for themselves and at worst introduce errors and misconceptions like reconciling a number to itself.

Brian,

dringstrom | Sat, 09/03/2013 - 12:44 | Permalink

Brian,

The upside/downside to Excel is that there are numerous ways to accomplish the same task. INT(NOW()) certainly accomplishes the task, but TODAY() gets it done with one function, and will be easier for most users to remember. In any case, thanks for enriching my article with a great alternative.

David

Nuncle,

dringstrom | Sat, 09/03/2013 - 12:49 | Permalink

Nuncle,

In over two decades as a spreadsheet consultant and trainer I've observed that even experienced users have a surprising number of blindspots in Excel. For instance, an article I wrote for AccountingWEB.com regarding hiding and unhiding rows and columns has had over 100,000 reads as of this writing.

David

Duncan,

dringstrom | Sat, 09/03/2013 - 12:50 | Permalink

Duncan,

Agreed. However, most users don't/won't do this, hence my ability to make a living writing about and presenting lectures on Excel.

David