Hello, I'm trying to write a formula that will recalculate a date based on a fixed number of days.
For example I have 2/1/19 in cell A42. In cell A41 I would like to show what that date would be if I subtracted 1 day, the result being 1/31/19.
But what, there's more. If. using the example above, the result for A41 was a weekend or holiday, I would like the formula to calculate that and back that date up to the next weekday/non holiday.
I have a spreadsheet I can provide if anyone would like to see it.
Formula for A41:
=WORKDAY.INTL(A42,-1,1,$B$1:$B$20)
start_date: take the date from A42.
days: subtract 1 day
[weekend]: the 1 here means weekend is Saturday and Sunday.
[holidays]: This is the range where you have listed the holidays. Adjust the range to match yours.
Perfect thanks Trowa. The only thing I'm not clear on is "[holidays]: This is the range where you have listed the holidays. Adjust the range to match yours." Can you help me understand?
Sure, lets say 1/28/2019 till 2/1/2019 is holiday. Then put 1/28/2019, 1/29/2019, 1/30/2019, 1/31/2019, 2/1/2019 in range B1:B5 and use that range as your holiday range. Use $ symbols to make the range fixed, so it stays the same when you drag the formula.
- Posts
- 2587
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 23, 2020
390