google-sheetstimegoogle-sheets-formuladuration

Specifying a time span / duration constant in Google Sheets formula over 24h


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?


Solution

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