Forum Discussion

Nora Connors's avatar
Nora Connors
Copper Contributor
Jun 29, 2018

Excel Formula to fill weekly calendar

Hoping to get help on attached excel doc:

1.  I need column D to NOT include US weekends or holidays.

2.  I need the cells in columns E - I to auto fill with green color based on the start and finish dates. I don't want to have to manually color each cell.  So when the start or finish dates change, the cells color automatically. 

Thank you so much in advance,

Nora Connors

nconnors100@gmail.com

Formulas & Functions
Need Help

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 29, 2018

    Hi Nora,

     

    To add workdays you may use WORKDAY() function https://exceljet.net/formula/add-business-days-to-date like

    =WORKDAY($B3-1,$C3)

    or more flexible one WORKDAY.INTL. 

    In above formula optional third parameter is the list of holidays which you shall to define first somewhere in your workbook.

     

    For the conditional formatting you may use the rule with the formula

    =($B3<=E$2+7)*($D3>=E$2)

    and apply the rule to your weeks range.

    Please check attached.

    • Nora Connors's avatar
      Nora Connors
      Copper Contributor
      Jun 30, 2018

      Hi Sergei,

      Thank you so much for helping me.  The Workday formula worked. Great!! But the formula for the weekday didn't quite work. It almost works, but something is off when I apply it to my real spreadsheet that I can't share.  I'll keep working on it.  Maybe I'm doing something wrong.

       

      Thanks, Nora

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 30, 2018

        Hi Nora,

         

        If you share the sample with data where the formula doesn't work I'll try to check what's wrong.

Resources