Forum Discussion

Leotings's avatar
Leotings
Copper Contributor
May 10, 2023

Help Function to Return Missing/Unavailable Data

I have the following table, and on each day the user will add a new row with the site and today's date.(along the rest of the info)
Every day, each of the three sites will add at least one row. I want to write a function that checks if, for today's date, Site A. B, or C actually added a new entry. If they didn't, then return a missing one. It also gets updated to "Present" once the user adds the data.
I tried different methods but wasn't able to do it. If it makes any difference, I will be linking this document to Power BI, so if its easier there do let me know how I can add it there too :')
 
Date               Site       Customer
08/05/2023
Site A
Customer A
08/05/2023
Site B
Customer C
08/05/2023
Site C
Customer V
08/05/2023
Site A
Customer A
09/05/2023
Site B
Customer A
09/05/2023
Site C
Customer D
09/05/2023
Site A
Customer F
10/05/2023
Site A
Customer C
10/05/2023
Site B
Customer A
10/05/2023
Site C
Customer B
 
 
excel
Formulas and Functions
power bi

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 10, 2023

    Leotings 

    =MAX(IF(B:B=B2,A:A))=TODAY()

    Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula returns TRUE (WAHR in german Excel) if there is an entry for today and FALSE if there isn't an entry for today.