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

Question

Hi, All&nbsp;We are using formulas to pull data from other sources to populate an 'as built' record of works completed on site.&nbsp; However, the surveyors working for the client keep predicting works for the future.&nbsp; Friday's sheet update shows works completed today (Monday), for example... and as with much in life, nothing is certain.&nbsp; 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...&nbsp; Basically not today or a future date.&nbsp;As ever, your help is greatly appreciated.&nbsp;NW&nbsp;=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))

hansvogelaar · Accepted Answer

Nick_W1890&nbsp;
Perhaps
&nbsp;
=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)&gt;=TODAY()),"",XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I))
&nbsp;
or if you have Microsoft 365 or Office 2021:
&nbsp;
=LET(d, XLOOKUP([@Name],'McLH As built - Pile logs'!A:A,'McLH As built - Pile logs'!I:I), IF(OR(d=0, d&gt;=TODAY()), "", d)

Forum Discussion

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

Share

Resources