Displaying dates for Sunday's and Thursday's

[Pippy79]

Hi,

I have a list of tasks that can be on different days of the week, but on Sundays and Thursday's the task is fixed. is there a way i can add it to the template but always fix the dates as Sundays & Thursday's?

example range of dates below;

Collection Date	Time
17/06/2023	12:00
16/06/2023	12:00
18/06/2023	10:00
19/06/2023	12:00
19/06/2023	14:00
20/06/2023	07:30
21/06/2023	07:30
24/06/2023	07:30
26/06/2023	07:30
27/06/2023	07:30
28/06/2023	07:30
29/06/2023	07:30
30/06/2023	07:30
30/06/2023	16:30
30/06/2023	21:00

 

[Kerryx]

=IF(OR(WEEKDAY(A4,2)=4,WEEKDAY(A4,2)=7),"fixed","whatever")

 

[Fluff]

Not sure what you are after, but maybe

Fluff.xlsm
	A	B	C
1	Collection Date	Time
2	17/06/2023	12:00	18/06/2023
3	16/06/2023	12:00	18/06/2023
4	18/06/2023	10:00	18/06/2023
5	19/06/2023	12:00	22/06/2023
6	19/06/2023	14:00	22/06/2023
7	20/06/2023	07:30	22/06/2023
8	21/06/2023	07:30	22/06/2023
9	24/06/2023	07:30	25/06/2023
10	26/06/2023	07:30	29/06/2023
11	27/06/2023	07:30	29/06/2023
12	28/06/2023	07:30	29/06/2023
13	29/06/2023	07:30	29/06/2023
14	30/06/2023	07:30	02/07/2023
15	30/06/2023	16:30	02/07/2023
16	30/06/2023	21:00	02/07/2023
Main

Cell Formulas
Range	Formula
C2:C16	C2	=WORKDAY.INTL(A2-1,1,"1110110")

 

[Pippy79]

Sorry, i wasn't very clear.

the range of dates above are the whole period of activities for a production run. within that range of dates i need to add activities on Thursdays and Sundays. The starting date can change but the fixed activities wont. So is it possible to take the starting date which can change and have a formula in a few cells that reads the start date and end date and populates the Thursdays and Sundays between and populates those dates with the fixed task. these dont have to be in order, the Thursday / Sunday Tasks can all be at the bottom as i can filter them in.

17/06/2023	Production Start
17/06/2023
18/06/2023 - Sunday	Fixed task
19/06/2023
19/06/2023
20/06/2023
21/06/2023
22/06/2023 - Thursday	Fixed task
25/06/2023 - Sunday	Fixed task
27/06/2023
28/06/2023
29/06/2023 - Thursday	Fixed task
30/06/2023	Production Finish

Thanks

 

[Fluff]

Did you try the formula from Kerryx?

 

[Pippy79]

I didn't understand what it was trying to tell me, i inputted it into cell B13 as below with the result shown in C1 for this example

A	B	C
1	17/06/2023	Production Start
2	17/06/2023
3	18/06/2023 - Sunday	Fixed task
4	19/06/2023
5	19/06/2023
6	20/06/2023
7	21/06/2023
8	22/06/2023 - Thursday	Fixed task
9	25/06/2023 - Sunday	Fixed task
10	27/06/2023
11	28/06/2023
12	29/06/2023 - Thursday	Fixed task
12	30/06/2023	Production Finish
13	=IF(OR(WEEKDAY(B1,2)=4,WEEKDAY(B1,2)=7),"fixed","whatever")	Displayed "whatever"

 

[Fluff]

You need to put it in C1 & drag down.

 

[Pippy79]

ah ok, so that works in terms of it highlights the cells which are Sundays / Thursday's.

Is it possible to have a formula (across multiple cells) at the bottom that take all the Thursdays and Sundays from the production date range and displays those dates. S

So a formula in Cell B14 that reads the dates B1:B13 and says there are X amount of thursdays and sundays and these dates are shown below.

A	B	C
1	17/06/2023	Production Start
2	17/06/2023
3	18/06/2023 - Sunday	Fixed task
4	19/06/2023
5	19/06/2023
6	20/06/2023
7	21/06/2023
8	22/06/2023 - Thursday	Fixed task
9	25/06/2023 - Sunday	Fixed task
10	27/06/2023
11	28/06/2023
12	29/06/2023 - Thursday	Fixed task
13	30/06/2023	Production Finish
14

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	1	17/06/2023	Production Start		4
2	2	17/06/2023			18/06/2023
3	3	18/06/2023	Fixed task		22/06/2023
4	4	19/06/2023			25/06/2023
5	5	19/06/2023			29/06/2023
6	6	20/06/2023
7	7	21/06/2023
8	8	22/06/2023	Fixed task
9	9	25/06/2023	Fixed task
10	10	27/06/2023
11	11	28/06/2023
12	12	29/06/2023	Fixed task
13	13	30/06/2023	Production Finish
14
Master

Cell Formulas
Range	Formula
E1	E1	=COUNTIFS(C:C,"Fixed task")
E2:E5	E2	=FILTER(B1:B100,C1:C100="Fixed task")
Dynamic array formulas.

 

[Pippy79]

Thanks Fluff, this has worked. Also Thanks Kerryx yours also worked