Match 2 sets of numbers in 2 cells

[Saher Naji]

I want to generate a special excel formula. My wish is to find out the last working day of each week. Thus, the formula is designed to analyze consecutive dates and find all Fridays, or, if there is no Friday for a given week, the last working day in the same week before Friday. The Friday or the nearest working day of a given week before Friday is marked with the symbol 1, other dates this week without the symbol ("") or a blank cell. See the attached screenshot for prepared results column. Note that sometimes there are several rows with the same date (red dates) and actually this is the problem which faced me. So, the job result is an excel formula to mark such days with symbol 1.

 

[ExcelGzh]

Try this formula =IF(OR(WEEKDAY(A1)<=WEEKDAY(A2),A1-A2>=7),1,"")

The logic is that the weekday number increases during the week Sun=1, Mon=2 etc. If the next date (A1) has a weekday number less than the current date (A2) then that means the next date is in next week and the current date is the end of this week. Also, if there are 7 days or more between dates then the current date is the end of this week.

 

[Snakehips]

I'm thinking....

Book1
	A	B	C	D	E	F	G
1			Formula	Result		Day
2	19/12/2019		1	1		Thursday	51
3	12/12/2019		1	1		Thursday	50
4	11/12/2019					Wednesday	50
5	09/12/2019					Monday	50
6	06/12/2019		1	1		Friday	49
7	03/12/2019					Tuesday	49
8	02/12/2019					Monday	49
9	26/11/2019		1	1		Tuesday	48
10	20/11/2019		1	1		Wednesday	47
11	18/11/2019					Monday	47
12	13/11/2019		1	1		Wednesday	46
13	11/11/2019					Monday	46
14	07/11/2019		1	1		Thursday	45
15	24/10/2019		1	1		Thursday	43
16	22/10/2019					Tuesday	43
17	21/10/2019					Monday	43
18	17/10/2019		1	1		Thursday	42
19	15/10/2019					Tuesday	42
20	11/10/2019		1	1		Friday	41
21	10/10/2019					Thursday	41
22	09/10/2019					Wednesday	41
23	03/10/2019		1	1		Thursday	40
24	03/10/2019		1	1		Thursday	40
25	01/10/2019					Tuesday	40
26	26/09/2019		1	1		Thursday	39
27	23/09/2019					Monday	39
28	17/09/2019		1	1		Tuesday	38
29	13/09/2019		1	1		Friday	37
30	12/09/2019					Thursday	37
Sheet5

Cell Formulas
Range	Formula
C2:C30	C2	=IF(A2="","",IF(WEEKNUM(A2)<>WEEKNUM(A1),1,IF(A2=A1,C1,"")))

Hope that helps.

 

[Saher Naji]

I'm thinking....

Book1
	A	B	C	D	E	F	G
1			Formula	Result		Day
2	19/12/2019		1	1		Thursday	51
3	12/12/2019		1	1		Thursday	50
4	11/12/2019					Wednesday	50
5	09/12/2019					Monday	50
6	06/12/2019		1	1		Friday	49
7	03/12/2019					Tuesday	49
8	02/12/2019					Monday	49
9	26/11/2019		1	1		Tuesday	48
10	20/11/2019		1	1		Wednesday	47
11	18/11/2019					Monday	47
12	13/11/2019		1	1		Wednesday	46
13	11/11/2019					Monday	46
14	07/11/2019		1	1		Thursday	45
15	24/10/2019		1	1		Thursday	43
16	22/10/2019					Tuesday	43
17	21/10/2019					Monday	43
18	17/10/2019		1	1		Thursday	42
19	15/10/2019					Tuesday	42
20	11/10/2019		1	1		Friday	41
21	10/10/2019					Thursday	41
22	09/10/2019					Wednesday	41
23	03/10/2019		1	1		Thursday	40
24	03/10/2019		1	1		Thursday	40
25	01/10/2019					Tuesday	40
26	26/09/2019		1	1		Thursday	39
27	23/09/2019					Monday	39
28	17/09/2019		1	1		Tuesday	38
29	13/09/2019		1	1		Friday	37
30	12/09/2019					Thursday	37
Sheet5

Cell Formulas
Range	Formula
C2:C30	C2	=IF(A2="","",IF(WEEKNUM(A2)<>WEEKNUM(A1),1,IF(A2=A1,C1,"")))

Hope that helps.

Thanks you very much,
Perfect 100%
Thanks Thanks ♥

 

[Saher Naji]

Try this formula =IF(OR(WEEKDAY(A1)<=WEEKDAY(A2),A1-A2>=7),1,"")

The logic is that the weekday number increases during the week Sun=1, Mon=2 etc. If the next date (A1) has a weekday number less than the current date (A2) then that means the next date is in next week and the current date is the end of this week. Also, if there are 7 days or more between dates then the current date is the end of this week.

thanks you very much,
I think we were on the right track, but there is a problem when we start a new year in the same list, but with Snakehips solutions it's working
thanks for all of you
Best Regards,