How to Check Whether Each Site Has Data for Today in WPS Office Spreadsheets
Question details
The user needs a formula to check whether each site in a list has at least one record dated today and return a present or missing status that updates automatically as rows are added.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user maintains a worksheet with dates in column A and site names in column B and wants to see, at a glance, whether each site has an entry for today.
- Observed behavior
- Each site should show a present or missing status for today's date, and the result should update automatically as new rows are added.
Make sure your worksheet contains dates in one column and site names in another, and that the dates are real Excel dates, not text. In WPS Office Spreadsheets, the same MAXIFS and array formulas used in Excel are available.
Use MAXIFS and TODAY to Check Today's Entries for Each Site
Use this formula when your WPS Office Spreadsheets or Excel version supports MAXIFS; it is the shortest and fastest way to return TRUE or FALSE for each site.
MAXIFS returns the latest date for the selected site, and comparing it with TODAY() checks whether that date is today. The formula returns TRUE when the latest record for the site was added today, which is the required present status.
In WPS Office Spreadsheets, open the workbook that contains dates in column A and site names in column B.
Select the first status cell, such as C2, type =MAXIFS($A:$A,$B:$B,B2)=TODAY(), and press Enter.
Drag the fill handle from C2 down through the remaining site rows, or double-click the fill handle to apply the formula automatically.
Press Ctrl+A to select the data range, go to Insert and select Table so the formula expands automatically when you add new rows.
Use an Array Formula for Older Excel Versions Without MAXIFS
Use this method if your version of WPS Office Spreadsheets or Excel does not support MAXIFS or if you need compatibility with older .xls workbooks.
Check Today's Site Entries Directly in WPS Office Spreadsheets
Open your existing Excel workbook in WPS Office Spreadsheets and use Excel-compatible MAXIFS, TODAY, and IF formulas to return a present or missing status for each site. WPS keeps the workflow familiar while remaining free and lightweight.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains your site and date columns.
- 2. Select the first status cell: Click the cell next to the first site where you want the result, such as C2.
- 3. Enter the formula: Type =MAXIFS($A:$A,$B:$B,B2)=TODAY() and press Enter.
- 4. Fill and verify the result: Drag the fill handle down the status column and confirm each site shows TRUE when its latest date is today.
- 5. Save the workbook: Press Ctrl+S or click Menu > Save to keep the formulas in WPS Spreadsheets or Excel format.
Frequently Asked Questions
Why does my MAXIFS formula return #NAME? in WPS Office?
MAXIFS requires a current version of WPS Office Spreadsheets or Excel. If you see #NAME?, use the array formula =MAX(IF($B$2:$B$1000=B2,$A$2:$A$1000))=TODAY() and confirm with Ctrl+Shift+Enter if required.
Can the status update automatically when I add rows for today?
Yes. If the formula uses full-column references like $A:$A and $B:$B, it updates automatically when you add rows. For older array formulas, use an Excel Table or expand the fixed range so new rows are included.
What does TRUE or FALSE mean in the result?
TRUE means the latest date for that site is today, so the site has at least one record dated today. FALSE means there is no record dated today for that site.
Does this work when dates are stored as text?
No, dates stored as text will not compare correctly with TODAY(). Convert them to real dates by selecting the column and using the Date format or Text to Columns, then check with the formula.
How do I return Present or Missing instead of TRUE or FALSE?
Wrap the formula in IF, for example =IF(MAXIFS($A:$A,$B:$B,B2)=TODAY(),"Present","Missing"). In older versions use =IF(MAX(IF($B$2:$B$1000=B2,$A$2:$A$1000))=TODAY(),"Present","Missing") and press Ctrl+Shift+Enter if required.
