Compare Time


Hi ,

i have a form with booked date ,start time and end time. once customer entered the information 'booked status`' will glow green , after that it has to compare with another booked date and timings ,if the timings matched then 'possibility ' column must glow red otherwise Green

in this example, i would like to show the first row 'possibility' must glow green, second row 'possibility' must glow red because the timings is inbetween 12 -14 (who already booked). can anyone please help me to solve this problem


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Insert an auto-number column (called "Auto") with no special formatting.

    Insert a text/number column (called "Start Helper") with this column formula:

    =VALUE(SUBSTITUTE([Start Time]@row, ":", ""))

    Insert a text/number column (called "End Helper") with this column formula:

    =VALUE(SUBSTITUTE([End Time]@row, ":", ""))

    Then try this column formula in the Possibility column:

    =IF(COUNTIFS([Book Date]:[Book Date], @cell = [Book Date]@row, [Start Helper]:[Start Helper], @cell <= [End Helper]@row, [End Helper]:[End Helper], @cell >= [Start Helper]@row, Auto:Auto, @cell < Auto@row) > 0, "Red", "Green")

  • PYI
    PYI ✭✭

    Perfect Mr. Paul,,, super!! ,, now my calender view looks like this,,,,

    1. Is it possible to show automatically this grey color to red in calenderview ?.... i tried out the conditional formating but it worked out only on sheets,, but in calander view i have to edit each bars manually using color setting.. is thr any workaround .

    2.can u pls a have look on this ticket,, then it will be really helpful for me to proceed further with this sheets..

    set time — Smartsheet Community

    Thank you very much for your wonderful support

  • PYI
    PYI ✭✭

    Hallo @Paul Newcome ,, Thank you very much,,, Yes i found it in the link... very useful,,,,

    now,,i expand the sheet with bit more functionality,,, while working on automation flow ,i had a problem,,

    Can you pls help me ...

    Thank you,,