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

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
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?
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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.
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

Similar discussions

encryption message several time in chat
measking -
HelpiOS -

1 response
IF statements with dates and criteria
jimbabama -
Mazzaropi -

3 responses
blank white page for website
yadaves -
ac3mark -

1 response
using web browser page loads but screen blank
inchoate -
leo -

16 responses
using nested if to blank out adjacent cells
WILLYB -
mubashir aziz -

3 responses