Calculate future dates

[jgalas]

Hi! I have a task that takes 50 hours. it can start on any business day and runs 2 hours on Tuesdays and 2 hours on Wednesdays. I want to know the end date of the task.
Ex: if the start date is 03/22/2023, the end date will be 06/20/2023 excluding a holiday on Tuesday (holidays and vacation days can be in a column)
can someone give me a tip please

 

[jgalas]

You will struggle to get it accurate as it will depend on whether the start is a Tue, Wed or Fri & what holidays you have.

Ok.
If have a column with dates for Mondays, another for Tuesdays, another for Wednesdays, others for Thursdays,... starting at 22/03/2023 and count Tuesday 2 hours, Thursday 1 hour and Friday 1 hour until make 50 hours and return the last date (holiday - 25/4/2023 in D2:D7) - end date 20/6/2023

 

[awoohaw]

@jgalas , i'm not sure how that will work. With WORKDAY.INTL I'm sure there is some way, with alot of crazy calcs and conditions.
there are probably some fancy array formulas that could do it as well, but those would take a long time to build, if even possible.

You have a tool with WORKDAY.INTL. I would be more than happy to see your solution when you have it.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

If you have 365 then try

=LET(d,WORKDAY.INTL(A2-1,SEQUENCE(100),"1001011",D2:D7),XLOOKUP(50,SCAN(0,WEEKDAY(d,2),LAMBDA(a,b,IF(b=2,a+2,a+1))),d,,1))

 

[jgalas]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I already updated my account data.
I use office 2013
Function "LET" and "LAMBDA", don't exist in my version.
Thanks for the time spent with me.

 

[Fluff]

I already updated my account data.

You only did that today, not when I asked.

 

[jgalas]

You only did that today, not when I asked.

Yes, i have already updated my account data TODAY
I only had the opportunity to return to this topic today. Sorry.

 

[Fluff]

Ok, how about

=INDEX(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D7),AGGREGATE(15,6,ROW(A1:A100)/(MMULT(--(ROW(A1:A100)>=TRANSPOSE(ROW(A1:A100))),IF(WEEKDAY(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D7),2)=2,2,1))>=50),1))

This may need to be confirmed with Ctrl Shift Enter, rather than just Enter.

 

[jgalas]

Ok, how about

=INDEX(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D7),AGGREGATE(15,6,ROW(A1:A100)/(MMULT(--(ROW(A1:A100)>=TRANSPOSE(ROW(A1:A100))),IF(WEEKDAY(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D7),2)=2,2,1))>=50),1))

This may need to be confirmed with Ctrl Shift Enter, rather than just Enter.

Don't work
I change it to:
=ÍNDICE(DIATRABALHO.INTL($L$7-1;LIN(A5:A104);"0101011";Data!$A$9:$A$100);AGREGAR(15;6;LIN(A5:A104)/(MATRIZ.MULT(--(LIN(A5:A104)>=TRANSPOR(LIN(A5:A104)));SE(DIATRABALHO(DIATRABALHO.INTL($L$7-1;LIN(A5:A104);"0101011";Data!$A$9:$A$100);2)=2;2;1))>=D14);1))

+ Ctrl Shift Enter

It give me 02/nov/2023 instead of 03/jul/2023

 

[Fluff]

This part LIN(A5:A104) needs to be LIN(A1:A100)