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.
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.
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.
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 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.