Time: MS Excel

  • by Carol Bratt on 20060808 @ 12:29AM EST | google it | send to friends
  • Filed under Carol's Corner Office | (related terms: cell, formula, serial, format, display)

There are several different methods of entering time into a cell. Take a look below to see them. After you've read all about it, open MS Excel and take them for a test drive. I find that if I use a function I'm more likely to remember it the next time I need it.

To enter the current time into a cell you can use a keyboard shortcut:

  • Select a cell and press CTRL + Shift + ;

To enter the current time into a cell by using a formula that returns the current date and time:

  1. Enter the formula: =NOW () 
     
  2. To get a formula that calculates the current time, change the format of the cell containing the NOW formula to h:mm in the Type box.
You can display the serial number behind the time. The time's serial number is between 0 and 1 (noon serial time 0.50). To display the serial number behind the time:
  1. Press CTRL + Shift + ; to insert the current time into cell A1.
     
  2. Press CTRL + ' to display the serial number of the current time.
     

Totaling Time Values

The time format to display hours, minutes and seconds is hh:mm:ss. For example, a time of 13 hours and 42 minutes is displayed as 13:42:00. The default time format does not allow a time value to exceed 24 hours. For example, by entering a time of 28:56:00, the result is 04:56:00. The way to get around that is to change the format of the cell by placing brackets around the hour, [HH]:MM:SS. The result is displayed as 28:56:00.

Calculating the Difference Between Hours

When you need to calculate your employees' hours for payroll, Excel can do it for you.

  • Open up a new spreadsheet and in B2 type EMPLOYEE
     
  • In C2 type Start Time.
     
  • In D2 type End Time.
     
  • In E2 type DIFFERENCE.
     
  • Next, in C3 type an employee name.
     
  • In C4 type 05:44.
     
  • In D4 type 10:40
     
  • In E4, type this formula: =D4-C4+IF(C4>D4,1) and depress your tab key.

(The number 1 in the IF formula equals 24 when dealing with time.) If you have entered all the data properly, your answer in E4 should be 4:56:00. To see the screenshot for this article click here.

Visit Carol's web site to learn more tips like this one!