Calculate a future working date and find the nearest furture Tuesday

[ian9999]

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

 

[FormR]

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.

 

[ian9999]

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

 

[FormR]

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
	A	B
2	28/03/2018	03/04/2018
Sheet1

Cell Formulas
Range	Formula
B2	=WORKDAY.INTL(A2,1,"1011111",holidays)

Named Ranges
Name	Refers To	Cells
holidays	=Sheet1!$D$2

 

[ian9999]

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

 

[FormR]

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
1	19/03/2018
2
3	7
4	28/03/2018
5	03/04/2018
Sheet1

Cell Formulas
Range	Formula
A4	=WORKDAY(A1,A3)
A5	=WORKDAY.INTL(A4,1,"1011111")