Logical functions with time format

[Gus_1967]

Hi all,

I am trying to come up with a function that looks at the time in a cell (in 24 hour format) and, depending on whether that time is between (08:00 and 18:00) or between (18:00 and 8:00 the next morning), then a rate is chosen from another cell.

In my limited knowledge of this subject, I tried:

=IF((B3<8:0 or B3>18:0),1, 0)

the 1, 0 was simply to sign a True or False to the condition so that the suitable rate can be selected (i.e., if the result is 1 then look at the value in X1, else look at the value in y1)

This yields an error message, so my next step was to go to the user guide and see if I could find anything there, but I’m not having any success.

I would be very grateful for any help, hints, or even just being pointed to where I can find information that will help.

Thanks,

Gus.

[Lupp]

I’m not quite sure if the already posted answers told you sufficiently clear that:

-1- Time values and date-time values are represented in spreadsheets by default as ordinary numbers using the day (1 d) as the mandatory unit.
-2- Comparisons, sorting and the like don’t depend on the used format as long as the representation is as described above.
-3- Problems with comparisons and sorting often just hint that the supposed values are texts in fact.
-4- A time value allowing to distinguish between 08:00 today and 08:00 next morning must have a day part (which probably hidden by bad formatting).
-5- Formats (in specific for date and time) are great liars generally, and very often misused.
-6- Reliable answers in the context are only possible based on a significant example sheet. Please make such an example available.

(Anyway OR cannot be used as an operator like you tried it in Calc.@robleyd already told you.)

[Gus_1967]

Thanks to robleyd, Opaque and Lupp.

With your help it is now clear, and the spreadsheet is looking good.

The cell in question ended up looking like this:

=IF(OR(B3<0.33,B3>0.75),“Non standard”,“Standard”).

I know that 08:00 should be 0.33333(infinite 3’s), but I figured that for the purposes of this exercise 0.33 was close enough.

Once again, thank you.

Gus.

[Lupp]

Quoting @gus_1967: “I know that 08:00 should be 0.33333(infinite 3’s)”…
There is no way to represent 8 h exactly as a fraction with the unit “day” in Calc. The best approximation you can get with the used ‘Double’ representation of numbers is what’s calculated for 1/3 - that only needs to type one 3 ;-).

Even the implemented comparison for “=” will account for the little difference it has to expect concerning two values both virtually meaning 1/3, but calculated in slightly different ways. This will, however, not cover the difference between 1/3 and 0.33333 (5 decimals) which is about 288 ms, and therefore often significant in a different context. You would need to write 15 digits “3” to be on the safe side.
Therefore better use =IF(OR(B3<1/3; B3>3/4; “Non standard”; “Standard”).
The logic applied here isn’t reliable anyway, if you not can assure that time-values given for a different day definitely cannot be inside the range from 08:00 through 18:00.

[anon73440385]

Hello,

a time is a fraction of a day. This means a full day of 24 hours is stored as 1, while 06:00 is 0.25, 12:00 is 0.50 and so on. Having said this, your IF() statement should read:

=IF(OR(B3<8/24;B3>18/24);1;0)

Ref.: Open Document Format for Office Applications (OpenDocument) Version 1.2 - Section 4.3.2 Time

Hope that helps.

[robleyd]

There are two aspects to your problem. First, OR is a function, not an operator. The Help tells us:

Returns TRUE if at least one argument
is TRUE. This function returns the
value FALSE, if all the arguments have
the logical value FALSE.

The arguments are either logical
expressions themselves (TRUE, 1<5,
2+3=7, B8<10) that return logical
values, or arrays (A1:C3) containing
logical values. Syntax

OR(LogicalValue1; LogicalValue2
…LogicalValue30)

LogicalValue1; LogicalValue2
…LogicalValue30 are conditions to be
checked. All conditions can be either
TRUE or FALSE.

Second, LO Calc stores times as a decimal part of a day where a day is 1; thus, for example 06:00 is stored as .25 You can then format the display of this underlying value in a number of ways, but the value remains .25 even if displayed as HH:MM

You can see this by changing the format of the cell in which you store the time to e.g. General.

Given that 08:00 is approximately 0.333333333333333 recurring, and many other times will be similar, consider using a helper cell to store the upper and lower times you want to test against. So assuming 08:00 in C1 and 18:00 in C2 your formula needs to be along the lines of

=IF(OR(B3<C1;B3>C2);1;0  )

If this answer helped you, please accept it by clicking the check mark ? to the left and, karma permitting, upvote it. That will help other people with the same question.

In case you need clarification, edit your question (don’t use an answer) or comment the relevant answer.