CATEGORY: Excel Time VERSIONS: All Microsoft Excel Versions

To quickly type time values into cells: Enter the following formula: =IF(A31000,TIMEVALUE(LEFT(A3,1)&":"&RIGHT(A3,2)),TIMEVALUE(LEFT(A3,2)&":"&RIGHT(A3,2))) The formatting of the cells in column B is HH:MM.

Screenshot // Converting a Number to a Time Value

Rate this tip 1 2 3 4 5   RATING: 2.43   VIEWS: 59463 Email Tip Print Tip Add to My Tips

READER COMMENTS (view all comments)

Error in formula Matt F wrote on December 31, 1969 19:00 EST There is an error in the formula but the correct formula is on the screen shot. Correct formula should be =IF(A3<1000,TIMEVALUE(LEFT(A3,1)&":"&RIGHT(A3,2)),TIMEVALUE(LEFT(A3,2)&":"&RIGHT(A3,2)))

REGISTERED USERS - Click here to post comments GUESTS (Click here to register) Name Comment Title Comments

Final Accounting: Ambition, Greed and the Fall of Arthur Andersen What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade Marketing Management Writing Excel Macros with VBA, 2nd Edition The Complete Book of Business Plans: Simple Steps to Writing a Powerful Business Plan (Small Business Sourcebooks) Analysis of Financial Statements

RELATED MICROSOFT EXCEL TIPS

Create User Forms in Microsoft Excel Using Loops in VBA in Microsoft Excel The Best Shortcut Keys in Microsoft Excel Writing Your First VBA Function in Excel Add Combo Box to a Sheet in Microsoft Excel Formulas to calculate Loan payments in Microsoft Excel Array variables using VBA in Microsoft Excel Create an Amortization Schedule in Microsoft Excel Find duplicates items in combine columns in Microsoft Excel Vlookup Formula – Organizing the Data Table

• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books