I have a work time sheet where I log all hours I worked for a given period of time. For a month, it sums around 160 hours. I would like to have a cell with how many hours I have left to work to fulfill my goal (160 hours) but I don't really need a cell holding the "160 hours" value.
Yet, I do have a single cell where I store the result of all hours I worked that given period.
What I can't do though, is specify in a formula the amount of hours I want to consider minus that total cell. I get weird values despite formatting the cell as "Number > Duration".
Suppose my current hour cell is K2, and it reads 96:58:00. Also Duration display.
To simplify, I will try to check how much is left to 100 hours.
First thing to come to mind is to use time() and specify 100 hours. Thus, I input the following formula in the next (L2) cell:
=time(100;0;0) - K2
Which surprisingly (to me!) results in:
-92:58:00
So it's clearly getting mod-24 (100%24=4) value in the time() function.
In fact, if I convert that into a value less than 24 hours, I get what I'd expect:
=time(20;0;0) - K2
would do the right math
-76:58:00
I checked a duration() function but it does something completely different by looking to its signature alone. Nothing like a timespan() function. timevalue() is also not something I could figure out as it converts a datetime value into an integer.
The only thing that worked thus far doesn't seem super elegant:
=(time(10;0;0)*10) - K2
Resulting in
3:02:00
Then some other things comes to mind:
=timevalue("100:00:00") => 0.16666667
=timevalue("10:00:00")*10 => 1.66666667
Replacing these numbers in the formula didn't help at all:
=0.16666667 - K2 => #ERROR!
=1.66666667 - K2 => #ERROR!
Is there a proper way to specify this 100 hour time span / duration in google sheets, or am I to stick to the multiplication trick?
The default time format is HH:mm:ss.sss or if only two factors are present, HH:mm. So, probably the one with the least surprise and easily understandable is
="100:00"-K2
where 100 is interpreted as hours and 00 is interpreted as minutes by inbuilt type switching.