Ask Your Question
0

Calc: time calculations [closed]

asked 2019-10-09 18:34:55 +0200

dave31175 gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-10-10 13:11:36.226196

2 Answers

Sort by » oldest newest most voted
1

answered 2019-10-09 19:00:42 +0200

erAck gravatar image

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())
edit flag offensive delete link more

Comments

Perfect, that's what I needed. Thanks!

dave31175 gravatar imagedave31175 ( 2019-10-09 21:28:28 +0200 )edit

Please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2019-10-09 21:54:21 +0200 )edit
1

answered 2019-10-09 18:42:26 +0200

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]...

edit flag offensive delete link more

Comments

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

dave31175 gravatar imagedave31175 ( 2019-10-09 18:54:40 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-10-09 19:16:17 +0200 )edit

Thanks all, I've got it now!

dave31175 gravatar imagedave31175 ( 2019-10-09 21:29:15 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2019-10-09 18:34:55 +0200

Seen: 28 times

Last updated: Oct 09