Compare Time

PYI

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

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

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

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 ...

https://community.smartsheet.com/discussion/84091/auto-workflow

Thank you,,