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.
To help avoid errors, he wanted to check if the current time was later than the start time.
Worksheet Time Calculations
The formula in cell C6 compares C4 to C2. I
- f 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.
Excel NOW Function
If we use the NOW function in C4, it includes the date, as well as the current time.
As a result, 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, in the screen shot below.
Formula to Remove Date Value
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
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 more examples of Excel Date and Time functions, go to the Excel Date and Time page on my Contextures site.
___________
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)
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.
Thanks Rick, and Chandoo, I’ve updated the link to your site.
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.
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/
Thanks Rick and Oscar!