Google spreadsheet, comparing durations

Question

I calculated a duration between two times, e.g. between 9:00 am and 11:00 am. So far so good. Now I need to decide if this duration is less 6 hours.

I do remember that this was pain in the s in excel but nevertheless I tried it the simple way:

=IF(E2 < 06:00:00; "y"; "n")

of course that didn't work. Next:

=IF(DURATION(E2) < DURATION(06:00:00); "y"; "n")

still, it didn't work.

So, okay, how can I compare two duration?

Insular

Answer 1

Divide hours by 24:

=IF(E2 < 6/24, "y", "n")

Value is E2 is a formatted time, actually 1 hour is 1/24, 1 day is 1.

Some info about date and time formats here:

http://www.excel-easy.com/examples/date-time-formats.html

Leeannaleeanne

Answer 2

You can also use the HOUR function if you want to

=if(HOUR(E2)<6,ʺyesʺ,ʺnoʺ)

or

=if(E2<time(6,0,0),ʺyesʺ,ʺnoʺ)

(if you write 06:00:00 in a formula it takes it as a string not a time)

but as I'm sure someone is about to point out, the first formula above gives the wrong answer for durations of more than a day (because it takes the hour part of a datetime).

What I find interesting is that you can assume for a worksheet formula that dates and times are represented as whole numbers (days) and fractions (parts of a day) just like in Excel. If you ever have to deal with them in Google App Scripts, you suddenly find that it's object-oriented and you have no choice but to use methods like hour() to manipulate them.

Joy

Answer 3

I needed to use the equivalent of:

=if(TIMEVALUE(E2)<6/24, "yes", "no")

Urumchi