Use the TIME function to combine single time parts - Date and Time Functions - Microsoft Excel Tutorials

Use the TIME function to combine single time parts using Microsoft Excel

The worksheet in Figure 4-29 shows single time parts in each column. Column B contains hours, column C contains minutes, and column D contains seconds. All three columns have to be combined into one time as shown in column E. To do this, use the TIME function. This function returns the decimal number for a particular time. The syntax is:

TIME(hour, minute, second)

hour: A number from 0 to 23 that represents the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value.
minute: A number from 0 to 59 that represents minutes. Any value greater than 59 will be converted to hours and minutes.
second: A number from 0 to 59 that represents seconds. Any value greater than 59 will be converted to hours, minutes, and seconds.

To combine single time parts into a valid time:

1. Select cells E2:E10 and type the formula =TIME(B2,C2,D2).
2. Press <-Ctrl+Enter->.
3. From the Format menu, select Cells.
4. Select the Number tab and click Custom under Category.
5. Enter hh:mm:ss as the custom format.
6. Click OK.

Use the TIME function to combine single time parts

Use the HOUR function to calculate with 100-minute hours - Date and Time Functions - Microsoft Excel Tutorials

Use the HOUR function to calculate with 100-minute hours using Microsoft Excel

Some companies record working time in 100-minute hours. For example, the time 6:45 is converted to 6:75, which sometimes makes further calculations easier. To convert to this format, extract the minutes from the time and divide them by 60 using the MINUTE function. This function returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59. The hours can be extracted with the HOUR function. This function returns the hour of a time value as an integer ranging from 0 (12:00 a.m.) to 23 (11:00 p.m.).

To convert normal time to 100-minute hours:

1. In cells A2:A10 list work dates.
2. In cells B2:B10 enter the start time for each day.
3. In cells C2:C10 record the end times.
4. Select cells D2:D10 and type the following formula: =HOUR(C2-B2)+MINUTE(C2-B2)/60.
5. Press <-Ctrl+Enter->.

Use the HOUR function to calculate with 100-minute hours