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 fini...
Formulas & Functions
Need Help
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.

      • Nora Connors's avatar
        Nora Connors
        Copper Contributor
        Jul 02, 2018

        Thank you SO much!  I'm on a freelance assignment and desperate to figure this out. attached is a new Excel file with my real data.  The calendar doesn't seem to populate correctly?

         

        Thanks again for the help!

Resources