Forum Discussion

JOHN_69's avatar
JOHN_69
Copper Contributor
Jun 23, 2022

HIGHLIGHTING WEEKEND CELLS IN A EXCELL SHEET

CAN'T REMEMBER HOWTO HIGHLIGHT WEEKENDS AND HOLIDAYS IN AN EXCELL SHEET. ALSO HIGHLIGHTING CERTAIN HOURS OF A DAY, LIKE AFTER BUSINESS HOURS.

Formulas and Functions

2 Replies

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Jun 24, 2022

    Hi JOHN_69 

     

    With the help of conditional formatting, you can achieve your requirements.

     

    1) Highlight the Weekend Cells
    Suppose column A contains the dates, select all data in this column and use the below formula as a conditional formatting rule.

    =OR(WEEKDAY(B1,2)=6,WEEKDAY(B1,2)=7)

     

    2) Highlight the Non-business Hours
    Suppose column D contains the times, select all data in this column and use the below formula as a conditional formatting rule.

    =OR(TEXT(D1,"HH:MM:SS")<TEXT($G$1,"HH:MM:SS"),TEXT(D1,"HH:MM:SS")>TEXT($G$2,"HH:MM:SS"))

     

    You may refer to the attached sample file for more clarity and understanding.

     

    Please let me know if it works for you.

     

    Thanks

    Tauqeer

    • jbeard34's avatar
      jbeard34
      Copper Contributor
      Jan 06, 2026

      Hello, "1) Highlight the Weekend Cells" worked but there is a typo - change "B1" to "A1" in the formula :).  Thanks!

Resources