Calculating the current date and time in an Excel spreadsheet

There are many scenarios where you may need to use the current date and time in your spreadsheets. You may simply need to display the current date in a spreadsheet report. Or, you may need to perform a calculation that uses the current date or time. This lesson shows you how to enter a formula into a cell in Excel that outputs the current date and/or time, and updates automatically as time passes.

How Excel stores dates and times

Before you start working with dates and times in Excel, it's useful to know how Excel stores them in your spreadsheets. This is for several reasons:

• Whenever you enter or view a date or time in Excel, you are actually viewing a number with a date or time format applied.
  • You can confirm this by typing a number (any number) into a cell in Excel and then apply a date format to it. The result may seem random - but it's not.
• Regardless of how you format dates and times, all calculations using dates and/or times are performed using the underlying numbers.
• You can format a cell to show only the date component or the time component of a date and time stored in that cell. However, that doesn't change the underlying number that is used when you perform a calculation on the value in the cell, which means you can end up with confusing, inaccurate results.

Let's look at a specific example of a date and time, and see how Excel sees this date.

• We'll work with 9:27 am on October 14th, 2013.
• The number that Excel actually stores when you type this date and time into a cell is 41561.39375. It doesn't matter how you format the cell - the number remains the same.
• The value before the decimal point - 41561 - is the date. It represents the number of days that have passed since January 1st, 1900.
  • At the risk of confusing you, there is an option in Excel to change your current worksheet to use January 2nd, 1904.
  • This might be an issue if you're working with others who use Excel on a Mac, but for the most part you shouldn't need to worry about it.
• The value after the decimal point - .39375 - is the time. It represents a percentage of 24 hours (i.e. the time in a day).
  • If you multiply 24 by .39375, you'll get 9.45, or 9 minutes and 45% of one hour (60 minutes).
  • Multiplying 60 by 45% gives you 27.
  • So .39375 actually equates to 9:27 AM.
  • In some cases you'll find the number of minutes and still have something left over - those are the seconds expressed as a percentage of 60. Our example didn't include seconds.

Now that you understand this, you're ready to continue the lesson.

The NOW() function

The NOW() function returns the current date and time. Unlike most functions in Excel there are no arguments needed for the NOW function. In other words, you don't need to provide any further information inside the brackets in order for the NOW function to work.

To display the current date and time in a spreadsheet, follow these steps:

• Select the cell where you want the time and date to be displayed.
• Enter =NOW() into the cell and press ENTER.
• The current date and time will be displayed.

You can then format the cell to change the way the date and time displays using the instructions below.

The TODAY() function

The TODAY() function is very similar to the NOW() function, except it only returns the current date.

To display the current date (and not the time) in a spreadsheet, follow these steps:

• Select the cell where today's date should be displayed.
• Enter =TODAY() into the cell and press ENTER
• The current date will be displayed.

You can then format the cell to change the way the date displays using the instructions below.

It's worth noting that the TODAY function works exactly the same way as the NOW() function except it returns the date and time at midnight at the start of today, whereas the NOW() function returns the current date and time.

Calculate the number of days before or after today

Another way you can use the TODAY function is to calculate how many days have passed since a previous date, or the number of days to go until a certain date.

• Enter the date you want to compare to today's date into a cell, for example cell A5 (you can also use a formula to calculate the date). Remember that by doing this, you are storing a number that is formatted like a date.
• In another cell, enter a formula such as =TODAY()-A5 (this would find the number of days since the date you typed into A5).
• You could calculate how many days into the future another date is by entering =A5-TODAY(), where A5 contains the future date.

Calculating the current time

As I noted already, the NOW() function gives you the current date and time. The TODAY() function gives just the current date, but not the time.

Unfortunately, there is no function in Excel to return just the current time. If you want to calculate the current time, you can use one of these formulas:

• =NOW()-TODAY()
• =MOD(NOW(),1)

The MOD() function is used for dividing numbers. It differs from regular division by returning only the remainder (i.e. what's left after performing the division calculation).

MOD() takes two arguments - the number to be divided and the divisor (the amount to divide the number by). These examples will help you understand how the MOD function works:

• =MOD(7,2) will return 1 (3*2=6, with 1 left over)
• =MOD(28,8) will return 4 (3*8=24, with 4 left over)

Looking back at the first MOD formula I gave you, here's how the MOD function will return just the time:

• The formula evaluates the NOW() function. Remember that Excel treats dates and times as numbers, so NOW() will return today's date and the current time.
• The formula then uses the MOD() function with 1 as a divisor to return just the value after the decimal point (i.e. just the current time).

And that's it - you've calculated the current time. You can now use it in your formulas to compare two times without having to worry about the date associated with each number.

Formatting cells that contain dates and times

• Right click the cell containing the date and/or time and choose Format Cells.
• Choose the Number tab, then choose the Date category (if you want to display just the current date) or the Time category (if you just want to display the current time).
• Pick a format that suits you, then click OK.
• Excel will display the current date and time in the format you chose.
• If you chose a Date format, Excel will not display the time (although the formula in the cell still calculates the time as well). The opposite is true if you choose a Time format.