Only populate a cell if the date is yesterday or older...

[Nick_W1890]

Hi, All

We are using formulas to pull data from other sources to populate an 'as built' record of works completed on site.  However, the surveyors working for the client keep predicting works for the future.  Friday's sheet update shows works completed today (Monday), for example... and as with much in life, nothing is certain.  The problem comes with our reporting of completed works as there are multiple graphs and durations etc. based on dates, so what I would like to do is insert a condition to the below function that will only populate the cell if the date (taken from a cell on the 'McLH As built - Pile logs' sheet - Column H) is yesterday or older...  Basically not today or a future date.

As ever, your help is greatly appreciated.

NW

=IF(XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I)=0,"",XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I))

[#2 · Accepted solution]

@Nick_W1890 

Perhaps

=IF(OR(XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I)=0,XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I)>=TODAY()),"",XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I))

or if you have Microsoft 365 or Office 2021:

=LET(d, XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I), IF(OR(d=0, d>=TODAY()), "", d)

[#3]

Amazing!

Thank you, Hans

Nick