3

I'm using a time-tracking sheet that calculates the time spent on a job by doing a simple subtraction between a start time and an end time in two cells.

The hh:mm format would suffice in this case, or as a small modification I'm using [h] "h" mm "m", but the issue with both is that I do not want the hours to display when there are none (when hours = 0).

If for example 35 minutes are logged I would like the output to be 35 m and not 0 h 35 m. The [mm] format might be an option then, but I would still like my time above 60 minutes to be displayed as 1 h 30 m, and not 90 m.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Tom
  • 33
  • 1
  • 3

2 Answers2

3

Please try:

[<0.04166666][mm] "m";[h] "h" mm "m"

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Works perfectly, thanks for the solution, just had to replace the . with a , after the zero duo to the system settings. – Tom Oct 01 '14 at 09:17
  • This worked for me, and I improved it for my own use by making it so only 1 digit is display for minutes, if its less than 10 minutes. Also, I made it display the seconds. If any wants to use this format: `[<0.00693][m]"m" ss"s";[<0.04166666][mm]"m" ss"s";[h]"h" mm"m" ss"s"` – Austin Whitelaw Dec 10 '20 at 16:24
0

Not at all elegant, but how about this:

=IF(HOUR(time)<1,MINUTE(time)&"m",HOUR(time)&"h "&MINUTE(time)&"m")

Returns a cell value 02:24:00 as "2h 24m" and a cell value 00:14:24 as "14m"

MikeyB
  • 301
  • 1
  • 2
  • 12