Calculate a future working date and find the nearest furture Tuesday

ian9999

New Member
Joined
Apr 5, 2008
Messages
48
Office Version
  1. 365
Platform
  1. Windows
Hi all

I am looking for some help, regarding trying to calculate the next Tuesday following a certain date.

If I explain the process it might be easier, currently we have a multi stage (7 in total), process that is connected to the previous stage, and 3 of these stages can only happen on a Tuesday, the other 7 must happen on a workday (Mon to Fri) excluding bank holidays, which is simple enough using the WORKDAY function and a list of dates for the bank holidays.

Start Date Number of process days (working only) Next Progress working Date Next Progress Tuesday Date19/03/2018 7 28/03/2018 03/04/2018
How do we calculate the next progress Tuesday date, plus as an added complication, what happens if the next Progress date lands on a bank holiday so this would need to find the next non bank holiday.

I hope I have explained this ok, I'm looking for any ideas you might have.

Thank you and regards :)

Ian
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi, if A2 contains the date you want to increment, you could try this.

=WORKDAY.INTL(A2,1,"1011111",holidays)

Note, holidays should refer to your list of holiday dates.
 
Upvote 0
Hi, if A2 contains the date you want to increment, you could try this.

=WORKDAY.INTL(A2,1,"1011111",holidays)

Note, holidays should refer to your list of holiday dates.


Thank you for your reply, I have used your formula and changed the first sections to reflect the date and number of days

A2 = 19/03/2018
,1, = 7

=WORKDAY.INTL(A2,7,"1011111",Holidays)

The result I would be expecting is 03/04/2018, but the formula result is 01/05/2018

Any ideas why this is happening?

Regards

Ian
 
Upvote 0
The result I would be expecting is 03/04/2018, but the formula result is 01/05/2018

You might need to explain why? The next Tuesday after the 19th March 2018 is the 20 March 2018?

EDIT: Did you in-fact want the next Tuesday after the 28th March 2018?


Excel 2013/2016
AB
228/03/201803/04/2018
Sheet1
Cell Formulas
RangeFormula
B2=WORKDAY.INTL(A2,1,"1011111",holidays)
Named Ranges
NameRefers ToCells
holidays=Sheet1!$D$2
 
Last edited:
Upvote 0
Hi Form R

Yes I did want the following Tuesday after the calculated date from A1 & A3 (This is where the 7 is located currently)

I will post the spread sheet when at home tonight, can't do this from my works PC

Regards

Ian
 
Upvote 0
I will post the spread sheet when at home tonight

Hi, the formula as posted (no need to alter the ,1,) will return the next working Tuesday from the date passed to it. If that's not what you want then a more detailed description will be needed along with any sample data.

I did want the following Tuesday after the calculated date from A1 & A3 (This is where the 7 is located currently)


Excel 2013/2016
A
119/03/2018
2
37
428/03/2018
503/04/2018
Sheet1
Cell Formulas
RangeFormula
A4=WORKDAY(A1,A3)
A5=WORKDAY.INTL(A4,1,"1011111")
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top