Forum Discussion
Excel Formula to fill weekly calendar
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.
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
Hi Nora,
If you share the sample with data where the formula doesn't work I'll try to check what's wrong.
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!
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)