» Entering Times Quickly
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
Entering times quickly, without having to use a colon to separate hours and minutes.
Solution:
Use the TIMEVALUE, IF, LEFT, and RIGHT functions as shown in the following formula:
=TIMEVALUE(IF(A2<1000, LEFT(A2,1),LEFT(A2,2))&":"&RIGHT(A2,2))
Enter the above formula in column B and format the cells as "hh:mm".
The formula will convert each numerical value in column A to a properly formatted time value.
Book Store:
enter HH:MM:SS quickly
Keith
Format A cells as Number/Custom 000000
Format B cells as General
Enter times 15:34:56 as 153456, 7:23:45 as 72345
Formula in B =
=MID(B4+10000000,3,2)&":"&MID(B4+10000000,5,2)&":"&RIGHT(B4,2)
Enter HHMMSS quickly
redchequer
Format A as Number/Custom 000000
Formar B as General
Enter times in A 15:45:12 as 154512, 7:56:45 as 75645
Formula in B =
=MID(B4+10000000,3,2)&":"&MID(B4+10000000,5,2)&":"&RIGHT(B4,2)
Reply: redchequer
Alan
Hi redchequer,
[QUOTE=redchequer]Format A as Number/Custom 000000
Formar B as General
Enter times in A 15:45:12 as 154512, 7:56:45 as 75645
Formula in B =
=MID(B4+10000000,3,2)&":"&MID(B4+10000000,5,2)&":"&RIGHT(B4,2)[/QUOTE]Is there a typo in there? This doesn't seem to work.
Alan.
Format B cells as General
Enter times 15:34:56 as 153456, 7:23:45 as 72345
Formula in B =
=MID(B4+10000000,3,2)&":"&MID(B4+10000000,5,2)&":"&RIGHT(B4,2)