Function Problems

Show or Hide Excel Data Based on the Current Day of the Week

Anum MushtaqAnum Mushtaq Nov 12, 2026 871 views

Question details

Learn how to control displayed Excel content according to the current day of the week.

Show or Hide Excel Data Based on the Current Day of the Week
Product
Microsoft Excel 365
Device & OS
not provided
Scenario
The user wants to show a message, return selected records, or visually mark data only on certain weekdays.
Observed behavior
The desired result is for workbook output or visible records to change based on the value returned by WEEKDAY(TODAY(),2).
Before you start

Decide whether you need to change a cell message, return a filtered list, or only highlight matching data. Use WEEKDAY(TODAY(),2), where Monday is 1 and Sunday is 7, when building weekday conditions.

Solution 1Recommended

Display content only on weekdays with an IF formula

Use this method when a cell should show a message, value, or calculation only from Monday through Friday.

The formula checks today’s weekday number. With the return type set to 2, Monday through Friday are 1 through 5, while Saturday and Sunday are 6 and 7.

1
Select the output cell

Click the cell where you want Excel to display the weekday-based result, such as B2.

2
Enter the weekday formula

Type =IF(WEEKDAY(TODAY(),2)<=5,"Show","Hide") and press Enter. Replace "Show" and "Hide" with your own text, cell reference, or calculation as needed.

3
Use a custom weekday rule if needed

For example, to show content only on Monday, enter =IF(WEEKDAY(TODAY(),2)=1,"Show","Hide"). To show content only on weekends, use =IF(WEEKDAY(TODAY(),2)>=6,"Show","Hide").

Display content only on weekdays with an IF formula
Formula recalculation: TODAY updates when the workbook recalculates or is reopened. Press F9 in Excel if you need to force recalculation.
Work smarter with WPS Spreadsheet

Use WPS Spreadsheet to Display Data for the Current Weekday

WPS Spreadsheet supports familiar Excel formulas such as TODAY, WEEKDAY, IF, and FILTER, helping you create weekday-based messages, dynamic record lists, and conditional formatting rules in a compatible spreadsheet workspace.

  1. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open your .xlsx or .xls workbook.
  2. 2. Create a weekday result formula: Click an output cell and enter =IF(WEEKDAY(TODAY(),2)<=5,"Show","Hide") to display a result on weekdays only.
  3. 3. Build a current-day record list: Click a blank output cell and use FILTER with a weekday helper column, such as =FILTER(A2:C100,B2:B100=WEEKDAY(TODAY(),2),"No records for today").
  4. 4. Apply a visual weekday rule: Select the target range, then choose Home > Conditional Formatting and add a formula rule based on WEEKDAY(TODAY(),2).
Create weekday rules with TODAY, WEEKDAY, and IF formulas.Filter records dynamically for the current day of the week.Open and edit Microsoft Excel .xlsx and .xls files with format compatibility.Use a familiar spreadsheet interface for formulas, filters, and conditional formatting.
microsoft office alternative - wps office

Frequently Asked Questions

What number does WEEKDAY(TODAY(),2) return for each day?

With return type 2, Monday is 1, Tuesday is 2, Wednesday is 3, Thursday is 4, Friday is 5, Saturday is 6, and Sunday is 7.

How can I show a value only on Friday in Excel?

Use an IF formula such as =IF(WEEKDAY(TODAY(),2)=5,"Your value",""), replacing "Your value" with text, a formula, or a cell reference.

Can conditional formatting hide rows based on the day of the week?

No. Conditional formatting can highlight or change the appearance of rows, but it cannot hide them. Use FILTER to generate a matching list, or apply an Excel table filter to limit visible records.

Why does my weekday formula not change at midnight?

The TODAY function changes when Excel recalculates. Reopen the workbook or press F9 to recalculate if automatic calculation is not updating the displayed result.