50. Excel Tips - Tell The Time in Excel
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:
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.
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.
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.
Download a spreadsheet to practice time functions in Excel
Training Video: Tell time in Excel
- Nick's blog
- Login or register to post comments
- 14354 reads
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)
Overtime
Here's an example: (Start Day is in A1)
Formula for Overtime:
=MAX(C2+D2-(A2+B2)-E2,0)
so, if you work less than 8 hours, no overtime, otherwise you get overtime