How does a "Date Look Up" formula work?

alyssa_f00

Recently, a smartsheet and a report I was added to has gone sideways and I can't figure out the triggers or formulas. The existing person left columns descriptions to help but even when following that the trigger isn't triggering…

Starting Column (date that tracks the starting date of someone's absence/leave) description: "Line with Date Look Up has =[Date Look Up]# with white text. This allows the date look up helper to always show 1"

Helper column description: "1= IF [starting date] matches [date look up] OR [starting date] is before [date look up] AND [returning date] is later than [date look up].
Line with Date Look Up has a formula to always show the [Date Look Up] value in the [starting date] in white"

and formula that is supposed to trigger what is needed:

=IFERROR(IF(OR(Starting@row = [Date Look Up]#, AND(Returning@row > [Date Look Up]#, Starting@row < [Date Look Up]#)), "1", "0"), "")

The report that feeds off our main sheet is broken and it has something to do with the trigger from the "Date Look Up" column and the helper column that feeds the info, but I have no idea where it's going wrong.

alyssa_f00

I'm available for a Teams/Zoom call too if I did a terrible job at explaining.

Paul Newcome

Are you able to provide screenshots for context?

Erin Horiuchi Green

Hello @alyssa_f00 ,

First, can you please provide your Sheet column format description?

Second, can you please provide any Sheet Automation logic description?

Third, can you please provide your Report designation description?