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.
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 |
Last edited: