Menu
Ask a question Report

Recalculation of dates [Solved]

Posts
3
Registration date
Tuesday December 4, 2018
Last seen
December 4, 2018
 - - Latest reply: BlairBiss
Posts
3
Registration date
Tuesday December 4, 2018
Last seen
December 4, 2018
 - Dec 4, 2018 at 12:35 PM
Excel Ask a question Report
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
See more 

Your reply

Reply to this topic

1 reply

Report Respond to TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
0
Thank you
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
Report Respond to BlairBiss
BlairBiss
Posts
3
Registration date
Tuesday December 4, 2018
Last seen
December 4, 2018
 -
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?
Report Respond to TrowaD
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
 -
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.
Report Respond to BlairBiss
BlairBiss
Posts
3
Registration date
Tuesday December 4, 2018
Last seen
December 4, 2018
 -
Awesome thanks
Respond to TrowaD

Your reply

Reply to this topic
Ask a question