Count deadlines help

[juanam]

I have created this excel sheet to count consecutive days from an initial date.
Although it works in most hypotheses, in some it gives me one day too many or one day too little.

Conditions (read carefully):
1) The period always begins counting on the first business day after the initial date and all days are counted including Saturdays, Sundays and holidays (except judicial fair and tourism week), but if that day is Saturday, Sunday holiday, judicial fair or tourism week, will begin to count from the first business day thereafter.
2) If the resulting date falls on a Saturday, Sunday, holiday, judicial fair or tourism week, it must be moved to the first subsequent business day.

Example 1: If the initial date is 06/20/2023, the days to count are 10 consecutive days, the resulting date will be 06/30/2023 (Saturdays and Sundays were counted and there was no holiday in between).

Example 2: The initial date is Friday 05/05/2023, 10 consecutive days will be counted, here as the next day is Saturday and the following Sunday, the counting must begin from Monday 05/08/2023, which will result in 05/17/2023.

Example 3: The initial date is Friday 05/19/2023, 5 consecutive days will be counted, but since the next day is Saturday, the next Sunday and Monday 05/22/2023 is a holiday, you have to start counting at starting on Tuesday, 05/23/2023, which will give a primary date resulting on Saturday, 05/27/2023, but since it falls on a Saturday, it must be moved to the first subsequent business day, which should result in Monday, 05/29/2023.

Example 4: Initial date Wednesday 06/28/2023, days counting 5, the counting begins from the next day that is Thursday 06/29/2023, then it is counted on Friday the 30th, but as from Saturday the 1st to Saturday the 15th of July is a judicial holiday, they are NOT counted, it would continue to be counted from Sunday the 16th and the resulting primary date would be Tuesday, 07/18/2023, but since that day falls on a holiday, it must be moved to the next business day, that is, the 07/19/2023.

To facilitate the work I have created 3 columns, one that contains the dates of the judicial fair and tourism week, another that contains the holidays and the last that contains all the non-working dates together.
I think the data columns that will be used will be the first and the last, the middle one is not very useful.

Count_deadlines.xlsx
	C	D	E	F	G	H
3	COUNT CONSECUTIVE DAYS
4
5	Initial date	30/06/2023	◄	Enter starting date
6	Consecutive days to add	3	◄	Enter days to count
7	Resulting date	here formula
8
9
10
11
12
13
14
15
16
17
18
19	Judicial Fair & tourism week	Holidays		All
20	01/01/2023	20/02/2023		01/01/2023
21	02/01/2023	21/02/2023		02/01/2023
22	03/01/2023	17/04/2023		03/01/2023
23	04/01/2023	01/05/2023		04/01/2023
24	05/01/2023	22/05/2023		05/01/2023
25	06/01/2023	19/06/2023		06/01/2023
26	07/01/2023			07/01/2023
27	08/01/2023			08/01/2023
28	09/01/2023			09/01/2023
29	10/01/2023			10/01/2023
30	11/01/2023			11/01/2023
31	12/01/2023			12/01/2023
32	13/01/2023			13/01/2023
33	14/01/2023			14/01/2023
34	15/01/2023			15/01/2023
35	16/01/2023			16/01/2023
36	17/01/2023			17/01/2023
37	18/01/2023			18/01/2023
38	19/01/2023			19/01/2023
39	20/01/2023			20/01/2023
40	21/01/2023			21/01/2023
41	22/01/2023			22/01/2023
42	23/01/2023			23/01/2023
43	24/01/2023			24/01/2023
44	25/01/2023			25/01/2023
45	26/01/2023			26/01/2023
46	27/01/2023			27/01/2023
47	28/01/2023			28/01/2023
48	29/01/2023			29/01/2023
49	30/01/2023			30/01/2023
50	31/01/2023			31/01/2023
51	02/04/2023			20/02/2023
52	03/04/2023			21/02/2023
53	04/04/2023			02/04/2023
54	05/04/2023			03/04/2023
55	06/04/2023			04/04/2023
56	07/04/2023			05/04/2023
57	08/04/2023			06/04/2023
58	01/07/2023			07/04/2023
59	02/07/2023			08/04/2023
60	03/07/2023			17/04/2023
61	04/07/2023			01/05/2023
62	05/07/2023			22/05/2023
63	06/07/2023			19/06/2023
64	07/07/2023			01/07/2023
65	08/07/2023			02/07/2023
66	09/07/2023			03/07/2023
67	10/07/2023			04/07/2023
68	11/07/2023			05/07/2023
69	12/07/2023			06/07/2023
70	13/07/2023			07/07/2023
71	14/07/2023			08/07/2023
72	15/07/2023			09/07/2023
73	25/12/2023			10/07/2023
74	26/12/2023			11/07/2023
75	27/12/2023			12/07/2023
76	28/12/2023			13/07/2023
77	29/12/2023			14/07/2023
78	30/12/2023			15/07/2023
79	31/12/2023			18/07/2023
80	01/01/2024			16/10/2023
81	02/01/2024			25/12/2023
82	03/01/2024			26/12/2023
83	04/01/2024			27/12/2023
84	05/01/2024			28/12/2023
85	06/01/2024			29/12/2023
86	07/01/2024			30/12/2023
87	08/01/2024			31/12/2023
88	09/01/2024			01/01/2024
89	10/01/2024			02/01/2024
90	11/01/2024			03/01/2024
91	12/01/2024			04/01/2024
92	13/01/2024			05/01/2024
93	14/01/2024			06/01/2024
94	15/01/2024			07/01/2024
95	16/01/2024			08/01/2024
96	17/01/2024			09/01/2024
97	18/01/2024			10/01/2024
98	19/01/2024			11/01/2024
99	20/01/2024			12/01/2024
100	21/01/2024			13/01/2024
101	22/01/2024			14/01/2024
102	23/01/2024			15/01/2024
103	24/01/2024			16/01/2024
104	25/01/2024			17/01/2024
105	26/01/2024			18/01/2024
106	27/01/2024			19/01/2024
107	28/01/2024			20/01/2024
108	29/01/2024			21/01/2024
109	30/01/2024			22/01/2024
110	31/01/2024			23/01/2024
111				24/01/2024
112				25/01/2024
113				26/01/2024
114				27/01/2024
115				28/01/2024
116				29/01/2024
117				30/01/2024
118				31/01/2024
Count_deadlines

Cells with Data Validation
Cell	Allow	Criteria
D5	Date	between 1/1/2023 and 30/12/2030
D6	Whole number	between 1 and 365

 

[braindiesel]

In my worksheet
O7 = Date in question
O8 represents days to add
L7:L105 = Your complete excluded date range
I suggest that you look at your holidays and when adding them to the exclusions, if they fall on a weekend, adjust them to the observed date, so if the date is July 1 and that is a Saturday, you put in July 3 on the list

Here is the ugly formula
=IF(WEEKDAY(WORKDAY(O7+1,O8,$L$7:$L$105)+O8-1+COUNTIFS($L$7:$L$105,">"&WORKDAY(O7+1,O8,$L$7:$L$105),$L$7:$L$105,"<="&WORKDAY(O7+1,O8,$L$7:$L$105)+O8-1),2)>5,WORKDAY(WORKDAY(O7+1,O8,$L$7:$L$105)+O8-1+COUNTIFS($L$7:$L$105,">"&WORKDAY(O7+1,O8,$L$7:$L$105),$L$7:$L$105,"<="&WORKDAY(O7+1,O8,$L$7:$L$105)+O8-1)+1,1,$L$7:$L$105),0)

It looks at the start date and then adds 1 business day, excluding your dates on your exclusion list

Then it adds the number of days minus 1 as we have our start date
Then it calculates the days on your exclusion list between the two dates and adds those
Then it checks if THAT date is a Saturday or a Sunday and if so, it goes to the next business day

I think that resolves your issue

 

[juanam]

...I suggest that you look at your holidays and when adding them to the exclusions, if they fall on a weekend, adjust them to the observed date, so if the date is July 1 and that is a Saturday, you put in July 3 on the list.

In this case, I cannot change the date of the judicial fair even if it falls on a Saturday, since from 07/01/2023 to 07/15/2023 it must be respected as it is because it is a date established by law in my country.


Example 1: It doesn't work, the resulting date should have been 06/30/2023 and with your formula it returns 07/31/2023.

Example 2: It doesn't work, the resulting date should have been 05/17/2023 and with your formula it returns 01/00/1900.

Example 3: It doesn't work, the resulting date should have been 05/29/2023 and with your formula it returns 01/00/1900.

Example 4: It doesn't work too, my friend, in this example I clarified that the resulting date should be 07/19/2023 and I explained the reasons.
With your formula it returns 07/24/2023.

I have seen that you have only used the "All" column and that will give incorrect results.

I have tried the formula with the 4 examples that I have shared and it has not worked for me.

I have copied and checked and translated the formula into Excell in Spanish, which was correct.

Formula translated into Spanish Excel with corrected cells and ranges:

=SI(DIASEM(DIA.LAB(D5+1;D6;$F$20:$F$118)+D6-1+CONTAR.SI.CONJUNTO($F$20:$F$118;">"&DIA.LAB(D5+1;D6;$F$20:$F$118);$F$20:$F$118;"<="&DIA.LAB(D5+1;D6;$F$20:$F$118)+D6-1);2)>5;DIA.LAB(DIA.LAB(D5+1;D6;$F$20:$F$118)+D6-1+CONTAR.SI.CONJUNTO($F$20:$F$118;">"&DIA.LAB(D5+1;D6;$F$20:$F$118);$F$20:$F$118;"<="&DIA.LAB(D5+1;D6;$F$20:$F$118)+D6-1)+1;1;$F$20:$F$118);0)

I have also changed the cells and range used to the original and it doesn't work.

 

[juanam]

To prove to you that I AM working on solutions and that I do not want you to do the work for me, I will share with you some of the 50 attempts I have made without obtaining the correct result.
As I have seen that it is very difficult to make a single formula that considers all the conditions without using macros, I have divided the work into 4 formulas placed in different cells.

I have always respected that the initial date is in C13 and the days to be counted in C14.
FORMULA 1 put in E13:

=SI(O(DIASEM(C13+1;2)>5;(COINCIDIR(C13+1; F20:F118; 0)=VERDADERO)); DIA.LAB.INTL(DIA.LAB.INTL(C13;C14;"0000000";Contar_plazos!$B$20:$B$57)-1;1;;Contar_plazos!$C$20:$C$27)+1; DIA.LAB.INTL(DIA.LAB.INTL(C13;C14;"0000000";Contar_plazos!$B$20:$B$57)-1;1;;Contar_plazos!$C$20:$C$27)+0)

With this formula I get it to count the days specified in C14, skipping Saturdays and Sundays.
IT DOESN'T WORK AT ALL! In C13 I put Friday 06/30/2023, the days to count in C14 were 3, which should result in Wednesday 07/05/2023, but it results in 07/04/2023.

To solve the previous problem I made this FORMULA 2 put in E14:

=SI(O(DIASEM(E13;2)>5;Y(NO(ESNUMERO(COINCIDIR(E13;F20:F118;0)))));DIA.LAB(E13+1;1;F20:F118);E13+SI(DIASEM(E13;2)=6;2;1))

WORKS! In E14 it gives me the result 07/05/2023, it counted the 3 days perfectly, skipping Saturdays and Sundays.

But since 07/05/2023 is one of the dates in the range F20:F118 that I must skip because all the non-business dates are there, I have created a FORMULA 3 set to F13 that works halfway:

=SI(O(ESNUMERO(COINCIDIR(E14;F20:F118;0));DIASEM(E14;2)>5);DIA.LAB(E14+SI(O(ESNUMERO(COINCIDIR(E14;F20:F118;0));DIASEM(E14;2)>5);1;0);1;F20:F118+2);E14)

IT HALF WORKS! In F13 the result is Tuesday 07/18/2023, that is, it counted correctly, it skipped the holidays that go from 07/15/2023 to 07/15/2023, but the correct result should be Wednesday, July 19.

To try to solve that I created FORMULA 4 and I have tried 4 variants WITHOUT success:

=SI(O(COINCIDIR(F13;F20:F118;0);DIASEM(F13;2)>5);DIA.LAB(F13+1;1;F20:F118+1);F13)

=SI(O(COINCIDIR(F13;F20:F118;0);DIASEM(F13+1;2)>5);DIA.LAB(F13+1;1;F20:F118+1);F13+1)

=SI(O(COINCIDIR(F13;F20:F118;0);DIASEM(F13+1)=7;DIASEM(F13+1)=1);DIA.LAB(F13+1;1;F20:F118+1);F13+1)

Instead of giving Wednesday, July 19, which would be correct, they all give me Thursday, July 20.

Well people, I have been trying things for +4 hours without success and this job has defeated me.
If someone has the desire and time to try to solve it, I would be very grateful. If they see that it is impossible, we will leave it like that, it is not possible.

 

[juanam]

SOLVED in another Excel forum, if you want can close this thread that I could not edit or delete.
Thank you

 

[Peter_SSs]

SOLVED in another Excel forum,

For the future then, please note the following.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Please provide a link to the cross-post in relation to this thread.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[juanam]

For the future then, please note the following.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Please provide a link to the cross-post in relation to this thread.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sorry again for violating that rule, this thread can now be closed.
Thank you