|

# » Entering Times Quickly

Problem:

Columns A & B contain pairs of numbers representing minutes and seconds.
Similarly, columns E & F contain pairs of numbers representing hours and minutes.
Some of the minute values in column A and hour values in column E represent time periods greater than 24 hours.
If we were to use simple TIME formulas to join each pair of values from columns A:B and E:F into single time values, they would return false results for all values exceeding 24 hours.
Thus, we are unable to simply use the following formulas to achieve our goal of a properly formatted time value:
=TIME(,A2,B2)
=TIME(E2,F2,)

Solution:

To join minutes and seconds, use the TIME and INT functions as shown in the following formula:
=TIME(,A2,B2)+INT(A2/1440)
Enter the above formula in column C and format as "[mm]:ss".
To join hours and minutes, use the TIME and INT functions as shown in the following formula:
=TIME(E2,F2,) + INT(E2/24)
Enter the above formula in column G and format as "[hh]:mm".
The above formulas will allow the user to enter times quickly, without having to use a colon to separate hours and minutes or minutes and seconds.

Rate This Tip
 1 2 3 4 5
Rating: 2.71     Views: 19465