» 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:
Recommended Books:
- Successful Business Planning in 30 Days: A Step-By-Step Guide for Writing a Business Plan and Starting Your Own Business
- Financial Shenanigans : How to Detect Accounting Gimmicks & Fraud in Financial Reports
- Wall Street Journal Guide to Understanding Money and Investing
- Microsoft Windows XP Inside Out
- The Complete Book of Business Plans: Simple Steps to Writing a Powerful Business Plan (Small Business Sourcebooks)
- The Fall of Advertising and the Rise of PR
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)