Time format of Pomodoro in LibreOffice Calc

I'm using LibreOffice Calc in Windows 7 and I'd like to write a formula that, given a decimal number, converts it to hours and minutes. For instance I have this: enter image description here

cell E3 is =($B$3*25/60) i.e. it returns the amount of hours corresponding to B3, the number of completed pomodori (4, in this case). One 'pomodoro' is equivalent to a 25 minute indivisible block, hence the 25 in the formula.

In cell F3 I've tried applying the [hh]:mm User-defined 'Cell Format' but I'm getting back '40:00' instead of 01:40, 1 hour and 40 minutes (0.67 * 60 ≈ 40 minutes). Can someone please help me? Is there a pre-configuered formula for this kind of calculation?

WobblyWindows

Posted 2015-03-03T14:41:55.923

Reputation: 101

1Try *=TIME(INT(B425/60), MOD(B425,60),0)* in F4, where F4 is having hh:mm format, to get the desired results. – Wishwas – 2015-03-03T15:41:40.787

That worked, thank you! Will it also work if I want to use this number to later do subtractions/additions? – WobblyWindows – 2015-03-03T17:27:53.050

Yes, it is working at least in the cases I tried: I copied the formula from F4 in F5. Then entered 5 in B5 cell. Now enter =F5-F4 in F6, =F4+F5 in F7. You will now see 2:05 in F5, :25 in F6, 4:10 in F7 etc. – Wishwas – 2015-03-05T16:19:09.170

Answers

Evidently E3 is in hours but spreadsheet time format works on a days and fractions of days. Assuming that F3 is equal to E3 but with the format [HH]:MM, then F3 should be divided by 24 to convert to days. Then the format returns 01:40.

W_Whalley

Posted 2015-03-03T14:41:55.923

Reputation: 3 212

your solution worked fine but can you please explain to me what was the problem in the first place? Why did I need to divide by 24 to get the correct form of hh:mm? What does it mean that the 'spreadsheet time format works on a days and fractions of days'? – WobblyWindows – 2015-03-03T17:30:13.013

2A date in calc is an integer that is the number of days from the arbitrary start date (12/30/1899 for my calc), so today is 42066, which can be formatted as 03/03/2015 00:00:00. To add one hour to that you would add 1/24 of a day, or 0.0416667. So the date of 42066.04166667 will format to 03/03/15 01:00. Your number was 1.67 hours so dividing by 24 converted 1.67 hours to the days scale that the date time format requires. – W_Whalley – 2015-03-03T19:04:04.417