Excel Tip: The power of NOW - Using the current time and date in Excel

Just a short post today because I have a very tight deadline to meet on a publication I am writing for the ICAEW (The Institute of Chartered Accountants here in England) on Automating Management Accounts in Excel.

Before we get into the post though, I have some exciting news for those of you who missed out on Mynda Treacy's Excel Dashboards course last time around. The course will be made available again for a limited time later this month. In the meantime, you can (re)read Mynda's guest post on the subject of Excel Dashboards here.

If you want to make sure you don't miss out this time, ensure that you are subscribed to the blog as I will keep subscribers updated. If you are not a subscriber, you can do so by leaving your email address in the form at the top right of the blog.

Today's post is a quick tip on how to use the current time and date in Excel.

There are two very similar functions that Excel provides for this - NOW and TODAY.

Both functions have no arguments but must still be followed by the (empty) brackets.

=NOW() returns the current date and time

=TODAY() returns the current date (actually it returns midnight at today's date)

I covered how Excel handles dates and time in a post a couple of weeks ago. Looking at the functions in those terms - NOW returns the full serial number for the current date and time and TODAY returns that serial number rounded down to the nearest whole number,

This functions can be used on their own, or as values in formulae, e.g using =TODAY()-A1 to calculate the elapsed time since the date in A1.

One caution to add to using these functions is that the values only update when Excel recalculates. Assuming you have the default calculation settings in Excel, recalculation will occur when any value changes in the spreadsheet, and when the spreadsheet is opened.

In most practical applications this is not a problem for the TODAY function unless the spreadsheet is open and unedited overnight. Obviously, it can be more of an issue with the NOW function.

You can, however, use the F9 key to force a recalculation at any point.

Right, back to Automating Management Accounts!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".