Calc: time calculations

In a time-formatted field (HH:MM), the formula “=TIME(17,0,0)-NOW()” returns hours and minutes from 17:00 to now. Can I use that to then multiply the number in another field by the number of minutes returned by that formula? For instance, the HH:MM displayed for that formula is 01:14. How can i then calculate k486*74?

1 Like

You multiply the duration by a number using the usual way: =K486*74 (given that K486 is the cell containing the =TIME(17;0;0)-NOW() formula you mentioned). The real question is how to format durations properly, so that they aren’t confused with day times. And the answer to that question is - use time duration format codes, which use square brackets to unlimit the most significant time part: [HH]:MM, or [MM], or [SS]

Thanks for your reply, however I believe I need further clarification, and perhaps I need to clarify my request.

M483, which is =TIME(17,0,0)-NOW() and when formatted as HH;MM it currently displays 05:12.

K486 is a general number field without a formula, displaying 0.9431

The formula =K486*M483 returns -41258, when the desired result would be 294,2472 (0.9431*312)

How would I make the formula return "minutes * 0.9431" as desired?

Thanks

As @erAck noted, using NOW() gives you full datetime (so regardless of what you format it to be, the cell internally has full number of days since null date). See his answer for the correct formula; and just format the result.

Thanks all, I’ve got it now!

All date and time values are numeric values just formatted differently, with the amount calculated in days, and 0.5 for example is 12 hours. To obtain the result in minutes you can either multiply by 24*60 (in your example =K486*24*60 or =K486*1440), or format the result with [MM] note the [] brackets for duration. Note also that your calculation may produce a negative value, which you don’t see with a HH:MM clock format.

However, your formula probably is not what you want to achieve, as the result of NOW() is a date+time value (since null date 1899-12-30) and right now =TIME(17;0;0)-NOW() unformatted gives -43747.0778472367 or -62995792.1000209 minutes…

If you use NOW() you have to subtract two full date+time values like

=DATE(2019;10;9)+TIME(17;0;0)-NOW()

or

=TIME(17;0;0)-(NOW()-TODAY())
1 Like

Perfect, that’s what I needed. Thanks!

Please, if the answer solves the question click :heavy_check_mark:.