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

  • James Keuning
    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 ##:##

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!