Display Particular Value if Number is greater than or equal (#) and Number is Less than or equal (#)

Mikaere Flavell

H7 (Yellow) needs to display to display G7(Green) if it D7(Blue) is less than or equal to 07:00 and greater than or equal to 04:00

Thanks for your help

James Keuning

=IF(AND(VALUE(LEFT([d7]@row, 2) + "." + RIGHT(VALUE((RIGHT([d7]@row, 2))) / 60, 2)) <= 7, VALUE(LEFT([d7]@row, 2) + "." + RIGHT(VALUE((RIGHT([d7]@row, 2))) / 60, 2)) >= 4), [g7]@row, 999)

The 999 at the very end is what shows up if d7 is NOT less than or equal to 07:00 and greater than or equal to 04:00

But that is probably really confusing...

This formula requires converts times to decimals, This is the piece that does the conversion:

VALUE(LEFT([d7]@row, 2) + "." + RIGHT(VALUE((RIGHT([d7]@row, 2))) / 60, 2))

So, if you replace that with something CONVERT, the formula becomes:

=IF(AND(CONVERTED <= 7, CONVERTED >= 4), [g7]@row, 999)

which is probably easier to understand.

So it's the formula that converts from time to decimal which does the heavy lifting here, and the approach I used requires two digits on each side of the colon. There are ways to make this more flexible, so that 5:15 will work as well as 05:15, but my solution assumes you will always have ##:##