Controlling Traffic Light Symbols Based on Comparison and Date Fields

Hello everyone,

I am attempting to control the "traffic light" symbols, Green, Yellow, Red, based on a comparison of one field to another and a date range.

Essentially it's a booking system where user inputs X and requests a date range. I'm trying to make it possible to book into the future and make my status symbol change colour based on whether it is available in this present moment (Of course you could simply look at the entries against the equipment and check the date fields)

I have two sheets. One is just a register for available equipment (lets call this sheet 1) and the other sheet is where the data comes in from the user (lets call this sheet 2).

A system administrator of some kind would then 'key' in the name of the equipment (in sheet 2)

Lets assume our equipment is called "EQUIPMENT1".

The status symbol in sheet 1 should then respond after comparing EQUIPMENT1 to EQUIPMENT1 on the other sheet and take into account the date. If Today's date is outside of the date range requested then the symbol will appear Green.

I think where I am running into problems is that its possible to have more than one of the EQUIPMENT1 in the column (with different dates). I say this because if I do some testing in a single sheet, with only one entry, the formulas work fine.

Here is an example of what I am trying.

=IF(AND([AssetNameSheet1]@row={AssetNameSheet2}@row), TODAY()<{NeedByDate}, TODAY()>{ReturnDate}), "Green" , "Red")

I hope what I am trying to Achieve is clear - if further clarification is needed please let me know.

Thank you all for your help.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!