1

I will explain this with an example:

Let's say I have a flow value, like 2 liter per hour. And let's say I have a container that is 3 liter big.

If I use a formula to get how much time I need to fill the container, as result 1.5 that is One hour and Half.

Instead of 1.5, I need to have an HH:MM:SS format, that will convert "1.5" to "1:30" or "1h 30m". I have looked all around stackoverflow and google, but seems I am the only one on earth with this need. There is a way to get this?

tbodt
  • 16,609
  • 6
  • 58
  • 83
Terix
  • 1,367
  • 5
  • 25
  • 39

2 Answers2

1

You can convert 1.5 to the time value 1:30 by dividing by 24, so you can do that in your initial formula, e.g. with flow = 2 in A2 and container size = 3 in B2 use

=B2/A2/24

custom format result cell as [h]:mm

barry houdini
  • 45,615
  • 8
  • 63
  • 81
1

I created a formula to convert a duration represented in hours as source.

It can display: [W]eeks, [D]ays, [h]ours, [m]inutes and [s]econds.

It works on Google Spreadsheet, but I think it should also work on Microsoft Office and Libreoffice Calc.

=CONCATENATE(IF(A1>=168,CONCATENATE(ROUNDDOWN(A1/168),"S "),""),IF(MOD(A1,168)>=24,CONCATENATE(ROUNDDOWN(MOD(A1,168)/24),"D "),""),IF(MOD(MOD(A1,168),24)>=1,CONCATENATE(ROUNDDOWN(MOD(MOD(A1,168),24)/1),"h "),""),IF(MOD(A1*60,60)>0,CONCATENATE(ROUNDDOWN(MOD(A1*60,60)),"m ","")),IF(MOD(A1*3600,3600)>0,CONCATENATE(ROUNDDOWN(MOD(MOD(A1*3600,3600),60)),"s","")))
carlososuna86
  • 96
  • 6