Pick a day of 25 weeks later

[secoo140]

Hello,
It is hard to explain, but I need to create a table, that says exact dates of some schecules.

The patients that I discharged from hospital
Needs to see me
in 25 weeks later, but exactly that week's Tuesday.

=Today()+175

this code not helps me because I discharge patient in different days.

I need a code like =blabla()+25weeks; tuesday.

Hope you understand, And help me
static work table makes cancer patients life harder.


Dr. Seckin.
Regards.

 

[steve the fish]

Try this:

=A1-WEEKDAY(A1,2)+177

where A1 contains your date.

 

[MARK858]

Or possibly...

=3-WEEKDAY(TODAY()+175)+TODAY()+175

depending on what you want it to produce when today's date is a Sunday.

 

[secoo140]

I tried both. not working.
I think we make a mistake.

 

[secoo140]

[COLOR=#333333][I]=3-WEEKDAY(TODAY()+175)+TODAY()+175[/I][/COLOR]

this code works
but how can I modify this as
next days of that week?

wednesday, friday - --- ?

 

[MARK858]

I don't understand what you are asking, the formula gives the Tuesday of the week whatever the day.

Excel Workbook
	A	B	C	D	E
1			MARK858		steve the fish
2	24/02/2018 Sat		14/08/2018 Tue		14/08/2018 Tue
3	25/02/2018 Sun		21/08/2018 Tue		14/08/2018 Tue
4	26/02/2018 Mon		21/08/2018 Tue		21/08/2018 Tue
5	27/02/2018 Tue		21/08/2018 Tue		21/08/2018 Tue
6	28/02/2018 Wed		21/08/2018 Tue		21/08/2018 Tue
7	01/03/2018 Thu		21/08/2018 Tue		21/08/2018 Tue
8	02/03/2018 Fri		21/08/2018 Tue		21/08/2018 Tue
9	03/03/2018 Sat		21/08/2018 Tue		21/08/2018 Tue
10	04/03/2018 Sun		28/08/2018 Tue		21/08/2018 Tue
Sheet1

 

[secoo140]

this works perfectly!

but today I managed to find another problem.
Some days are national holiday.
How to find these days / and skip that week?

is there any way to add this code "Skip these days"

 

[MARK858]

Probably a tidier way of doing this if I thought more about it but see if the below works for you.
The holidays are listed in E2:E4 (change to suit).
The 182 is what adds the 7 days (175 + however many days you need to add).

Excel Workbook
	A	B	C	D	E
1	Start date	Original formula	Adding 7 days to holidays		Holiday dates
2	24/02/2018	14/08/2018 Tue	14/08/2018 Tue		20/11/2018
3	25/02/2018	21/08/2018 Tue	21/08/2018 Tue		18/12/2018
4	25/02/2018	21/08/2018 Tue	21/08/2018 Tue		25/12/2018
5	26/02/2018	21/08/2018 Tue	21/08/2018 Tue
6	26/04/2018	16/10/2018 Tue	16/10/2018 Tue
7	27/05/2018	20/11/2018 Tue	27/11/2018 Tue
8	27/02/2018	21/08/2018 Tue	21/08/2018 Tue
9	30/06/2018	18/12/2018 Tue	25/12/2018 Tue
10	01/07/2018	25/12/2018 Tue	01/01/2019 Tue
Sheet1

 

[MARK858]

Just a fix for an issue with Xmas and New Year....

Excel Workbook
	A	B	C	D	E
1	Start date	Original formula	Adding 7 days to holidays		Holiday dates
2	24/02/2018	14/08/2018 Tue	14/08/2018 Tue		20/11/2018
3	25/02/2018	21/08/2018 Tue	21/08/2018 Tue		18/12/2018
4	25/02/2018	21/08/2018 Tue	21/08/2018 Tue		25/12/2018
5	26/02/2018	21/08/2018 Tue	21/08/2018 Tue
6	26/04/2018	16/10/2018 Tue	16/10/2018 Tue
7	27/05/2018	20/11/2018 Tue	27/11/2018 Tue
8	27/02/2018	21/08/2018 Tue	21/08/2018 Tue
9	30/06/2018	18/12/2018 Tue	08/01/2019 Tue
10	01/07/2018	25/12/2018 Tue	08/01/2019 Tue
11	08/07/2018	01/01/2019 Tue	08/01/2019 Tue
Sheet1

 

[Tetra201]

A shorter one:

=WORKDAY.INTL(A2+170,1,"1011111",Holidays)

where Holidays is a range that contains holiday dates.