How to Enter Time Without a Colon and Calculate Decimal Hours in Excel
Question details
The user wants to input time values as raw numbers (e.g., 1730 instead of 17:30) to save typing, have them recognized visually as time, and then calculate the elapsed duration between two times in decimal hours (e.g., 45 minutes as 0.75 hours).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Rapidly entering timesheet or log data without typing standard delimiters like colons, and then calculating elapsed work hours for payroll or tracking.
- Observed behavior
- When typing plain numbers, Excel treats them as standard integers. The user needs a way to display them as time and apply a mathematical calculation to extract the decimal hour difference.
Ensure your numeric time data is entered consistently (e.g., always use 3 or 4 digits, like 830 or 1730) so that formatting rules and formulas can correctly interpret the values.
Use Formulas to Convert Numbers and Calculate Decimal Hours
Convert plain numerical entries into recognizable time values within a formula, then multiply by 24 to extract the exact decimal hours between a start and end time.
Excel natively treats plain numbers (like 1730) as integers, not times. By using the TEXT function, you can force Excel to interpret the number as a time value. Because Excel calculates time as a fraction of a 24-hour day, multiplying the result by 24 converts the time difference into standard decimal hours.
Click on the cell where you want the calculated decimal hours to appear.
Type the formula `=(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"))*24`, assuming B2 is your end time (e.g., 1730) and A2 is your start time (e.g., 0830).
Press Enter to apply the formula. Excel will subtract the start time from the end time and multiply the fraction by 24.
Right-click the result cell, select 'Format Cells', choose the 'Number' category, set decimal places to 2, and click OK to display the result as decimal hours (e.g., 8.50).
Apply Custom Number Formatting for Displaying Time
If you only need the raw number to visually look like a time format (displaying 1730 as 17:30) without changing the underlying value, use Custom Cell Formatting.
Easily Format Time and Calculate Hours in WPS Spreadsheet
WPS Spreadsheet fully supports all standard Excel formulas, custom formatting rules, and advanced calculations, allowing you to manage time tracking and timesheets seamlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open your timesheet document.
- 2. Enter raw time numbers: Type your numeric times (e.g., 1730, 0830) rapidly into your designated columns without colons.
- 3. Calculate total hours: Use the formula `=(TEXT(EndCell,"00\:00")-TEXT(StartCell,"00\:00"))*24` to compute the time difference.
- 4. Format the output: Select your result cells, press Ctrl+1 to open Format Cells, and set the category to Number to view your decimal hours.
Frequently Asked Questions
Why do I need to multiply by 24 to get decimal hours?
Spreadsheet software stores dates and times as serial numbers where one full day equals 1. Therefore, one hour represents 1/24th of a day. By multiplying a calculated time fraction by 24, you convert the time value into whole decimal hours.
Can I format '1730' directly into a real time value without a formula?
Custom formatting (like 00\:00) only changes how the number looks on your screen, not the underlying integer value. To convert it into a true time value that the software recognizes for standard time math, you must use a formula (like the TEXT function) or a VBA script.
How do I calculate decimal hours if a shift goes past midnight?
When a shift crosses midnight, the end time appears smaller than the start time, resulting in a negative calculation. To fix this, use the MOD function to wrap the calculation around 24 hours. The formula would look like: `=MOD(TEXT(EndCell,"00\:00") - TEXT(StartCell,"00\:00"), 1)*24`.
