I have a spreadsheet with the following relevant data:
| A | B | C |
--|------|------|-----|
1 | Time |Number|Const|
2 |01:00 | 100 | 5 |
3 |01:30 | | |
4 |02:00 | | |
In this setup, the values in row 2 are static. However, the rest of the cells in column B need to be calculated, by taking their adjacent time values and obtaining the difference between it, and A2. The difference in minutes then needs to be multiplied by the constant in C2 to obtain a numerical format value.
Right off the bat, performing the following will not work in B3:
=A3 - $A$1 * $C$2
What I'm trying to get as a result would be the difference of half an hour in minutes, multiplied by 5, which should be 30*5 for 150.
I figured it wouldn't be that simple, as I need a way for Calc to turn a time value into a usable value. To that end, I've also tried the following formulas:
=TIME(,A3-$A$1,)*$C$2
=TIME(,A3,)-TIME(,$A$1,)*$C$2
The first gives me 502 error, so I'm sure that's not the right way to do it. The second gives me a value of 0, in much the same way that the very first formula without the TIME formula had output.
I also tried using the TIMEVALUE formula, but referencing any time format cell with that formula just gives me a NAME error.
What other ways can I try to do this?
If your values in column A are recognised as times then it's easy for LibreOffice.
If I type 01:00 in a cell, it recognises it as a time, and changes it to 01:00:00
If you need it to be formatted exactly as 01:00 you can right click on the cell and choose the appropriate format. In this case, the cell looks like 01:00 but if you click in it, the value bar says 01:00:00
You can then do maths with these time values.
So =A3-A2 is calculated as 00:30:00
You could almost use the MINUTE() function, but don't. MINUTE() would work if all of your intervals are less than 1 hour. Bit if the interval is greater than an hour then MINUTE() only gives the minute portion of it (so if the interval works out to 01:30, then MINUTE() will give you 30 which isn't what you want).
Instead, you actually use the way that dates are stored. It appears LibreOffice stores dates in the same way Microsoft does. It's a floating point number. The integer part is days, the floating point part is fraction of the day.
So if you right click on a cell with a time value 00:30, and choose format / number / general, it will render 0.208333. You can convert that into minutes by multiplying it by 1440 - that is 24*60 - that converts your fraction of a day, into number of minutes, which works even when the interval is greater than 1 hour.
So the formula in B3 becomes:
=B2 + ((A3-A2)*1440)*$C$2
You can copy this formula down - the $C$2 format means that it continues to reference the C2 cell. Omitting the $ makes it relative, so if you copy this and paste it into B4, LibreOffice will change it to
=B3+((A4-A3)*1440)*$C$2