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
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!

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 03, 2018

      Hi Nora,

       

      To return the correct end date for the projects wit zero duration

      =WORKDAY($D4-($E4>0),$E4)

      Conditional formatting I applied for wider range

      =$J$4:$AZ$666

      and adjust the rule formula to exclude empty dates

      =($D4<=J$2+7)*($F4>=J$2)*($D4>1)
      • Nora Connors's avatar
        Nora Connors
        Copper Contributor
        Jul 04, 2018

        Works great now.  I can't thank you enough.  How kind of you Sergei to help a stranger.  Wishing you blessings.  Nora

Resources