Planning Your Day with Excel 2007's NOW, TIME, and TIMEVALUE Functions

Excel 2007's time functions enable you to convert text entries representing times of day into time serial numbers so that you can use them in calculations. Some common time functions include NOW, TIME, and TIMEVALUE.

NOW

The NOW function gives you the current time and date based on your computer’s internal clock. You can use the NOW function to date- and time-stamp the worksheet. Like the TODAY function, NOW takes no arguments and is automatically recalculated every time you open the worksheet:

=NOW()

When you enter the NOW function in a cell, Excel puts the date before the current time. It also formats the date with the first Date format and the time with the 24-hour Time format. So, if the current date were July 23, 2009 and the current time was 1:44 p.m., at the moment when Excel calculates the NOW function, your cell would contain the following entry:

7/23/2009 13:44

Note that the combination date/time format that the NOW function uses is a custom number format. If you want to assign a different date/time format to the date and time serial numbers returned by this function, you have to create your own custom number format and then assign it to the cell that contains the NOW function.

TIME

The TIME function enables you to create a decimal number representing a time serial number, ranging from 0 (zero), representing time 0:00:00 (12:00:00 a.m.), to 0.99999999, representing 23:59:59 (11:59:59 p.m.). You can use the TIME function to combine the hours, minutes, and seconds of a time into a single time serial number when these parts are stored in separate cells.

The TIME function takes the following arguments:

=TIME(hour,minute,second)

When specifying the hour argument, you use a number between 0 and 23 (any number greater than 23 is divided by 24 and the remainder is used as the hour value). When specifying the minute and second arguments, you use a number between 0 and 59 (any minute argument greater than 59 is converted into hours and minutes, just as any second argument greater than 59 is converted into minutes and seconds).

For example, if cell A3 contains 4, cell B3 contains 37, and cell C3 contains 0, and you enter the following TIME function in cell D3:

=TIME(A3,B3,C3)

Excel enters 4:37 a.m. in cell D3. If you then assign the General number format to this cell (Ctrl+Shift+` or Ctrl+~), it would then contain the time serial number, 0.192361.

TIMEVALUE

The TIMEVALUE function converts a time entered or imported into the worksheet as a text entry into its equivalent time serial number so that you can use it in calculations. The TIMEVALUE function uses a single time_text argument as follows:

=TIMEVALUE(time_text)

So, for example, if you put the following TIMEVALUE function in a cell to determine the time serial number for 10:35:25:

=TIMEVALUE(“10:35:25”)

Excel returns the time serial number 0.441262 to the cell. If you then assign the first Time number format to this cell, the decimal number appears as 10:35:25 a.m. in the cell.

dummies

Source:http://www.dummies.com/how-to/content/planning-your-day-with-excel-2007s-now-time-and-ti.html