Forum Discussion

Nick_W1890's avatar
Nick_W1890
Copper Contributor
Feb 13, 2023

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

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

  • 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)

  • 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)

Resources