Count deadlines help

juanam

New Member
Joined
Aug 1, 2021
Messages
28
Office Version
  1. 2021
Platform
  1. Windows
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
CDEFGH
3COUNT CONSECUTIVE DAYS
4
5Initial date30/06/2023Enter starting date
6Consecutive days to add3Enter days to count
7Resulting datehere formula
8
9
10
11
12
13
14
15
16
17
18
19Judicial Fair & tourism weekHolidaysAll
2001/01/202320/02/202301/01/2023
2102/01/202321/02/202302/01/2023
2203/01/202317/04/202303/01/2023
2304/01/202301/05/202304/01/2023
2405/01/202322/05/202305/01/2023
2506/01/202319/06/202306/01/2023
2607/01/202307/01/2023
2708/01/202308/01/2023
2809/01/202309/01/2023
2910/01/202310/01/2023
3011/01/202311/01/2023
3112/01/202312/01/2023
3213/01/202313/01/2023
3314/01/202314/01/2023
3415/01/202315/01/2023
3516/01/202316/01/2023
3617/01/202317/01/2023
3718/01/202318/01/2023
3819/01/202319/01/2023
3920/01/202320/01/2023
4021/01/202321/01/2023
4122/01/202322/01/2023
4223/01/202323/01/2023
4324/01/202324/01/2023
4425/01/202325/01/2023
4526/01/202326/01/2023
4627/01/202327/01/2023
4728/01/202328/01/2023
4829/01/202329/01/2023
4930/01/202330/01/2023
5031/01/202331/01/2023
5102/04/202320/02/2023
5203/04/202321/02/2023
5304/04/202302/04/2023
5405/04/202303/04/2023
5506/04/202304/04/2023
5607/04/202305/04/2023
5708/04/202306/04/2023
5801/07/202307/04/2023
5902/07/202308/04/2023
6003/07/202317/04/2023
6104/07/202301/05/2023
6205/07/202322/05/2023
6306/07/202319/06/2023
6407/07/202301/07/2023
6508/07/202302/07/2023
6609/07/202303/07/2023
6710/07/202304/07/2023
6811/07/202305/07/2023
6912/07/202306/07/2023
7013/07/202307/07/2023
7114/07/202308/07/2023
7215/07/202309/07/2023
7325/12/202310/07/2023
7426/12/202311/07/2023
7527/12/202312/07/2023
7628/12/202313/07/2023
7729/12/202314/07/2023
7830/12/202315/07/2023
7931/12/202318/07/2023
8001/01/202416/10/2023
8102/01/202425/12/2023
8203/01/202426/12/2023
8304/01/202427/12/2023
8405/01/202428/12/2023
8506/01/202429/12/2023
8607/01/202430/12/2023
8708/01/202431/12/2023
8809/01/202401/01/2024
8910/01/202402/01/2024
9011/01/202403/01/2024
9112/01/202404/01/2024
9213/01/202405/01/2024
9314/01/202406/01/2024
9415/01/202407/01/2024
9516/01/202408/01/2024
9617/01/202409/01/2024
9718/01/202410/01/2024
9819/01/202411/01/2024
9920/01/202412/01/2024
10021/01/202413/01/2024
10122/01/202414/01/2024
10223/01/202415/01/2024
10324/01/202416/01/2024
10425/01/202417/01/2024
10526/01/202418/01/2024
10627/01/202419/01/2024
10728/01/202420/01/2024
10829/01/202421/01/2024
10930/01/202422/01/2024
11031/01/202423/01/2024
11124/01/2024
11225/01/2024
11326/01/2024
11427/01/2024
11528/01/2024
11629/01/2024
11730/01/2024
11831/01/2024
Count_deadlines
Cells with Data Validation
CellAllowCriteria
D5Datebetween 1/1/2023 and 30/12/2030
D6Whole numberbetween 1 and 365
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
...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. o_O

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

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:
VBA Code:
=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.
 
Upvote 0
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:
VBA Code:
=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:
VBA Code:
=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:
VBA Code:
=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:
VBA Code:
=SI(O(COINCIDIR(F13;F20:F118;0);DIASEM(F13;2)>5);DIA.LAB(F13+1;1;F20:F118+1);F13)
VBA Code:
=SI(O(COINCIDIR(F13;F20:F118;0);DIASEM(F13+1;2)>5);DIA.LAB(F13+1;1;F20:F118+1);F13+1)
VBA Code:
=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.



Ícono de validado por la comunidad
 
Upvote 0
SOLVED in another Excel forum, if you want can close this thread that I could not edit or delete.
Thank you (y)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top