50. Excel Tips - Tell The Time in Excel

Nick's picture


Here are a couple of useful Time-related tips for Excel.

The first one is the NOW Excel function
Lets see how it works..

Step1:
Enter the following formula in any cell:
=NOW()
For me right now, this returns: 03/02/2009 09:22

NOTE - if the column width is too small, you will see something like this:
tell-the-time-in-excel
All you have to do in this case is to widen the column.
The best way to do this is to select the offending cell, and press in sequence: ALT then O then C then A
- this autofits the column and will now show the time.
tell-the-time-in-excel

Step2:
Format the cell (right click => format cells => custom) to a good format like:
"ddd dd-mmm-yy hh:mm:ss"
For me right now, this returns:
"Tue 03-Feb-09 09:22:17"
NOTE - NOW is accurate to the nearest second.
tell-the-time-in-excel
The NOW() function in Excel is Volatile, which means that it will recalculate when Excel recalculates .
Be careful on using it with too many dependents, as it can cause sluggish calculation speed.

The second part of this tip is that there is a shortcut to put the time directly into a cell..
Press: CTRL and SHIFT and ;
For me right now, this returns: 09:22:00
NOTE - this is the static time for when you press the shortcut keys

Strangely, the TIME function doesn't actually return the time, however you can use it to tell the time by using NOW as an input.

Here's how:
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
... clearly a better way to do this is to put =NOW() in another cell, and link to that.

tell-the-time-in-excel

Download a spreadsheet to practice time functions in Excel

Training Video: Tell time in Excel

how to Calculate Overtime in Excel

I am trying to calculate overtime in a length of day. I want to know how I can do it in excel.

If 8:00 am to 5:00 pm is a regular time and rest is considered to be overtime, then how it will be extracted automatically from the given times, for example:

Start time 7:am
Finish time: 5:30 pm
Overtime: 1:30 (one hour and thirty minutes).

OR

Start time 11:59 pm
Finish time 8:00 am
Overtime: 8:01 (Eight hours & one minute)

Nick's picture

Overtime

Here's an example:  (Start Day is in A1)

Start Day Start Time End Day End Time Hours per day Overtime
26-Jan-11 16:00 27-Jan-11 02:00 08:00 02:00

Formula for Overtime:

=MAX(C2+D2-(A2+B2)-E2,0)

so, if you work less than 8 hours, no overtime, otherwise you get overtime