Forum Discussion
Excel Project due dates that fall on a monday, weekend or holiday roll back to previous friday
I am creating project task matrix in excel. All "Client Due Dates" are based on the starting date (Project Task 1"). If a "Client Due Date" is due on a monday or weekend, the "Internal Due Date" should roll back to the previous friday.
I used 2 formulas for this:
"Project Task 5": "Client Due Date" falls on a MONDAY, I want the "Internal Due Date" to roll back to the Previous Friday. The formula I used is =IF(WEEKDAY(C6)=2,C6-3,C6)
"Project Task 4 AND 5": "Client Due Date" fall on the Weekend. I want the "Internal Due Date" to roll back to the Previous Friday. The formula I used is =C5-WEEKDAY(C5,2)+5
I want to combine the two formulas. In other words, if a "Client Due Date" falls on a weekend OR a monday, the "Internal Due Date" will be the previous Friday.
I could change the number of days in the "B" column, however, this plan will have 100+ "Client Due Dates" over several months and it's not efficient.
Anyone know the "magic formula" that is eluding me? Also, I would like Holidays excluded, however, I won't lose sleep if this can't be done.
Thanks
Melissa
| A | B | C | D | |
| 1 | Project Task | Days | Client Due Date | Internal Due Date |
| 2 | Project task 1 | 1 | Tuesday, January 2, 2018 | Tuesday, January 2, 2018 |
| 3 | Project task 2 | 3 | Friday, January 5, 2018 | Friday, January 5, 2018 |
| 4 | Project task 3 | 4 | Saturday, January 6, 2018 | Friday, January 5, 2018 |
| 5 | Project task 4 | 5 | Sunday, January 7, 2018 | Friday, January 5, 2018 |
| 6 | Project task 5 | 6 | Monday, January 8, 2018 | Friday, January 5, 2018 |
| Date | Holiday | |||
| Monday, January 1, 2018 | New Year’s Day | |||
| Monday, January 15, 2018 | Birthday of Martin Luther King, Jr. | |||
| Monday, February 19, 2018 | Washington’s Birthday | |||
| Monday, May 28, 2018 | Memorial Day | |||
| Wednesday, July 4, 2018 | Independence Day | |||
| Monday, September 3, 2018 | Labor Day | |||
| Monday, October 8, 2018 | Columbus Day | |||
| Monday, November 12, 2018 | Veterans Day | |||
| Thursday, November 22, 2018 | Thanksgiving Day | |||
| Tuesday, December 25, 2018 | Christmas Day |
Hi!
You can use this formula:
[D2] : =C2-(WEEKDAY(C2,12)>4)*WEEKDAY(C2,16)
And drag it down.
I don't understand this part:
"... Also, I would like Holidays excluded"
What is your expected result? Blessings!
John,
Where may I buy your magic wand? The formula worked perfectly. Thank you, you Excel warrior!
Regarding ""... Also, I would like Holidays excluded"
What I meant: if the "Client Due Date" falls on a Holiday (on a Wednesday, for example), the "Internal Due Date" will roll back to the day before the holiday (in this example, Tuesday).
Thank you so much for your help. That formula was way over my head!
You're welcome!
For last comment, you can use this formula instead:
=C2-(WEEKDAY(C2,12)>4)*WEEKDAY(C2,16)-COUNTIF(C$11:C$20,C2-(WEEKDAY(C2,12)>4)*WEEKDAY(C2,16))
Blessings!
