How to represent time durations (e.g. time spent), in Excel, in hours-minutes-seconds?

Question

Our platform has Excel reports with fields like Estimated Time or Actual Time spent on a task. These fields are not standardized - in some reports, time values are represented as 10.8 mins, 0.22mins, or 1400.5mins (which are not very human-readable, e.g., user has to mentally convert 0.8mins to seconds). In other places, these are rounded off to integer values in minutes (which results in imprecise data).

Is there a standard way of representing these time durations in Excel? I tried storing the data in excel as Number -> Time -> hh:mm:ss, but such formatted cells also contain a default date prefix of 01-01-1900. As a result, the cell cannot store values like 26:01:05 (26hours 1minute 5seconds)

Answer 1

What kind of reports are those?

If they are technical and accurracy is important, they should not be in Excel and they should not be edited manually. Get a program and a real database.

If they are for timekeeping of people's activities, how do you even spend 0.8 minutes? That is madness. Tracking it in the timesheet takes more time than that and certainly at less precision.

Pick a unit of time you want to measure. Think about a common sense minimum. For example, it does not make any sense to track time below a minute for normal working day routines. Even waiting for the elevator to get to the meeting room could take longer. Measure something meaningful. Minutes might be a good start. Just name the field "Minutes". People know what minutes are and what happens after 60. Movies routinely classify as "90 Minutes" or "110 Minutes" without people flinching or having a math stroke.

If you have bigger units, lets say "Days", you really need to rethink your "Minutes" minimum. If someone puts in "8 days", what does it matter whether you spent 10 or 15 minutes on that other task? Maybe "Hours" would be the better unit. 8 days seems pointless anyway, since you don't know whether that were 8 days of slacking off at 6 hours, or 8 days or sweaty overtime at 12 hours a day.

So to summarize, find a unit of time that makes sense to measure. Measure that. And only that.

Sidenote: the fact that it is not standardized at all, tells me it is a write-only field. You are supposed to write something into it, because the creator of the form saw it somewhere and thought it was neat, but nobody will ever read that field. Otherwise, they would have complained about how pointless that data is if it is not in meaningful units.