Previous Topic Next Topic

Recalculation of dates

Solved/Closed
BlairBiss Posts 3 Registration date Tuesday December 4, 2018 Status Member Last seen December 4, 2018 - Dec 4, 2018 at 11:04 AM
BlairBiss Posts 3 Registration date Tuesday December 4, 2018 Status Member Last seen December 4, 2018 - Dec 4, 2018 at 12:35 PM
Hello,

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.

Is this possible? Thanks in advance everyone.

System Configuration: Macintosh / Safari 12.0.1
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 4, 2018 at 11:43 AM
Hi Blair,

You are looking for the WORKDAY.INTL function.

WORKDAY.INTL(start_date, days, [weekend], [holidays])

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.

Drag formula up.

Best regards,
Trowa
0
BlairBiss Posts 3 Registration date Tuesday December 4, 2018 Status Member Last seen December 4, 2018
Dec 4, 2018 at 11:45 AM
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?
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 4, 2018 at 11:51 AM
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.
0
BlairBiss Posts 3 Registration date Tuesday December 4, 2018 Status Member Last seen December 4, 2018
Dec 4, 2018 at 12:35 PM
Awesome thanks
0

Similar discussions

If a cell contains any text then add a value (with dates)
ryannordgren -
ryannordgren -

7 responses
Excel VBA Code Date Filter between two dates
Bhaskar -
Basit -

1 response