How to enter time durations in Excel without reference to a date or time of day, just tracking hours and minutes

Excel can store and calculate durations reliably, but it always stores time as a fraction of a day. That means six hours and fifty minutes is stored as 6.8333… ÷ 24, even if the display looks different. The key is to accept Excel’s underlying time-serial system and then control only the display.

You can enter duration directly by typing “6:50” into a cell. Excel interprets this as six hours and fifty minutes because there is no AM/PM. The underlying value will look like 0.2847… in the formula bar if the cell is formatted as a number, but you can format it to show whatever duration style you want.

To display “6h 50m” without affecting the stored value, use a custom number format: [h]"h "mm"m" The “h” and “m” are placed inside brackets so hours do not wrap at 24. This is the same approach used for timesheets and project-tracking. It is not a workaround; it is the intended method for displaying elapsed time.

With that custom format applied, the cell still behaves like a real duration and works cleanly in formulas like AVERAGE, MIN and MAX. For example, if A1:A5 contain durations: =AVERAGE(A1:A5) =MIN(A1:A5) =MAX(A1:A5) All three will return correct results. You can apply the same custom duration format to the result cells so the output also appears as “xh ym”.

If you want to enter durations using text such as “6h 50m”, Excel cannot directly parse that. You would need either formulas or Power Query to convert text into time serials. The cleanest long-term solution is to enter durations using 6:50 or 6.833 and rely on the custom duration format above for display.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin