For a Good Time Call Excel

Yes, Excel can help you have a good TIME. It’s also useful if you’re looking for a DATE, or the perfect MATCH. (Insert your own bad puns here.)

Today we’ll focus on the TIME function, because one of my clients recently wanted some help with that.

What Time Is It?

There are a couple of keyboards shortcuts for entering the date or time.

  • To enter the current date on a worksheet, press the Ctrl key, and tap the ; key.
  • To enter the current time, press Ctrl and Shift, and tap the ; key

If you want the date or time to update when you open the workbook, you can use a formula instead.

  • To enter the date with a formula, type:  =TODAY()
  • To enter the date and time with a formula, type: =NOW()

Is It Too Late?

My client’s workbook had a cell with a process start time. He wanted to check if the current time was later than the start time.

Compare Times in Excel

The formula in cell C6 compares C4 to C2. If the time in C4 is greater than the time in C2, the result is TRUE. Because C4 is empty in this screen shot, the result is FALSE.

If we use the NOW function in C4, it includes the date, as well as the current time. Even though the time in C4 is 8:17 AM, its value is much higher than the 9:00 AM time in C2.

I’ve added temporary formulas in column D, so you can see the numeric value for each cell in column C.

Now Function in Excel

It’s About Time

To calculate the current time value in C4, we can change the formula, to remove the date value.

The TRUNC function removes the fractional part of a number, so we’ll subtract TRUNC(NOW()) from the NOW function.

=NOW()-TRUNC(NOW())

Translated to English, the formula is: (DATE and TIME) minus DATE

Now Trunc Function

The revised formula leaves the time value only in C4, and now the value in D4 is lower than the value in D2.

The formula in C6 now shows the correct result of FALSE.

More Date and Time Info

For the ultimate source of Excel Date and Time information, go to Chip Pearson’s Dates and Times in Excel page. Chip explains:

  • how Excel’s date system works
  • date and time arithmetic
  • how to calculate working times
  • formulas to find out when the next holiday occurs
  • using dates and times in VBA
  • and even the elusive DATEDIF function

On his blog, Chandoo, at Pointy Haired Dilbert, shows how to calculate Thanksgiving dates, although it might be a bit late to figure out when Thanksgiving is this year.

___________

You may also like...

6 Responses

  1. Rick Rothstein (MVP - Excel) says:

    Instead of doing it this way…

    =NOW()-TRUNC(NOW())

    you can eliminate one volatile function call by doing it this way instead…

    =MOD(NOW(),1)

  2. Chandoo says:

    Hi Debra.. thanks for the material and linking to thanksgiving date formula page. Can you please update the URL to http://chandoo.org/wp/2009/11/25/findout-thanksgiving-date/

    there was a scheduling mistake that caused this problem.

  3. Thanks Rick, and Chandoo, I’ve updated the link to your site.

  4. Rick Rothstein (MVP - Excel) says:

    Debra and Chandoo…

    Here is a slightly shorter way to calculate Thanksgiving Day (US and Canada) that uses one less function call…

    US: =DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3))
    Canada: =DATE(A1,10,15)-WEEKDAY(DATE(A1,10,6))

    The generic version of these formulas (which I first saw posted online by Peo Sjoblom, although I don’t know if it is original with him or not), which can be used to find the Nth such and such day of a given month for a given year, looks like this…

    =DATE(Year,Month,1+7*NthDay)-WEEKDAY(DATE(Year,Month,8-DayOfWeek))

    So, just plug in the year, month, NthDay and DayOfWeek (1 for Sunday, 2 for Monday, etc.) and perform the indicated math. As an example, for this year’s Thanksgiving in the US…

    Year = 2009
    Month = 11
    DayOfWeek = 5 (for Thursday)
    NthDay = 4 (for 4th Thurday of the month)

    which, when substituted into the generic version of formula, gives…

    =DATE(2009,11,1+7*4)-WEEKDAY(DATE(2009,11,8-5)

    which reduces to the formula I indicated earlier.

  5. Oscar says:

    There is a good blog post about time stamps using circular formula on PHD: http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/

Leave a Reply

Your email address will not be published. Required fields are marked *