Display Particular Value if Number is greater than or equal (#) and Number is Less than or equal (#)
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
Answers

=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 ##:##
Help Article Resources
Categories
Check out the Formula Handbook template!