Calculate future dates

jgalas · May 4, 2023

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

Fluff · May 9, 2023

What is your start date?
And what if any holidays do you have?

jgalas · May 9, 2023

Fluff said:
What is your start date?
And what if any holidays do you have?

starting date 22/03/2023
None working days:
01/abr to16/abr
25/abr
01/mai
08/jun
10/jun
13/jun

Fluff · May 9, 2023

Thanks for that.
With those dates I get 7th Jul.
Columns F & G show the list of dates & the cumulative hours.

Fluff.xlsm

A

B

C

D

E

F

G

H

1

Dates

Hours

Day

2

22/03/2023

01/04/2023

1

3

02/04/2023

24/03/2023

2

Fri

4

03/04/2023

28/03/2023

4

Tue

5

04/04/2023

29/03/2023

5

Wed

6

05/04/2023

31/03/2023

6

Fri

7

06/04/2023

18/04/2023

8

Tue

8

07/04/2023

19/04/2023

9

Wed

9

08/04/2023

21/04/2023

10

Fri

10

09/04/2023

26/04/2023

11

Wed

11

10/04/2023

28/04/2023

12

Fri

12

11/04/2023

02/05/2023

14

Tue

13

12/04/2023

03/05/2023

15

Wed

14

13/04/2023

05/05/2023

16

Fri

15

14/04/2023

09/05/2023

18

Tue

16

15/04/2023

10/05/2023

19

Wed

17

16/04/2023

12/05/2023

20

Fri

18

25/04/2023

16/05/2023

22

Tue

19

01/05/2023

17/05/2023

23

Wed

20

08/06/2023

19/05/2023

24

Fri

21

10/06/2023

23/05/2023

26

Tue

22

13/06/2023

24/05/2023

27

Wed

23

26/05/2023

28

Fri

24

30/05/2023

30

Tue

25

31/05/2023

31

Wed

26

02/06/2023

32

Fri

27

06/06/2023

34

Tue

28

07/06/2023

35

Wed

29

09/06/2023

36

Fri

30

14/06/2023

37

Wed

31

16/06/2023

38

Fri

32

20/06/2023

40

Tue

33

21/06/2023

41

Wed

34

23/06/2023

42

Fri

35

27/06/2023

44

Tue

36

28/06/2023

45

Wed

37

30/06/2023

46

Fri

38

04/07/2023

48

Tue

39

05/07/2023

49

Wed

40

07/07/2023

50

Fri

41

11/07/2023

52

Tue

Master

Cell Formulas
Range	Formula
F2:G101	F2	=LET(d,WORKDAY.INTL(A2-1,SEQUENCE(100),"1001011",D2:D50),xx,XLOOKUP(50,SCAN(0,WEEKDAY(d,2),LAMBDA(a,b,IF(b=2,a+2,a+1))),d,,1),HSTACK(d,SCAN(0,WEEKDAY(d,2),LAMBDA(a,b,IF(b=2,a+2,a+1)))))
H2:H101	H2	=TEXT(INDEX(F2#,,1),"ddd")
B2	B2	=LET(d,WORKDAY.INTL(A2-1,SEQUENCE(100),"1001011",D2:D50),XLOOKUP(50,SCAN(0,WEEKDAY(d,2),LAMBDA(a,b,IF(b=2,a+2,a+1))),d,,1))
B3	B3	=INDEX(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D50),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:D50),2)=2,2,1))>=50),1))
Dynamic array formulas.

Excel Formula:

=ÍNDICE(DIATRABALHO.INTL(A2-1;LIN(A1:A100);"1001011";D2:D50);AGREGAR(15;6;LIN(A1:A100)/(MATRIZ.MULT(--(LIN(A1:A100)>=TRANSPOR(LIN(A1:A100)));SE(DIA.SEMANA(DIATRABALHO.INTL(A2-1;LIN(A1:A100);"1001011";D2:D50);2)=2;2;1))>=50);1))

jgalas · May 9, 2023

Fluff said:
Thanks for that.
With those dates I get 7th Jul.
Columns F & G show the list of dates & the cumulative hours.

Fluff.xlsm
A B C D E F G H
1 Dates Hours Day
2 22/03/2023 07/07/2023 01/04/2023 22/03/2023 1 Wed
3 07/07/2023 02/04/2023 24/03/2023 2 Fri
4 03/04/2023 28/03/2023 4 Tue
5 04/04/2023 29/03/2023 5 Wed
6 05/04/2023 31/03/2023 6 Fri
7 06/04/2023 18/04/2023 8 Tue
8 07/04/2023 19/04/2023 9 Wed
9 08/04/2023 21/04/2023 10 Fri
10 09/04/2023 26/04/2023 11 Wed
11 10/04/2023 28/04/2023 12 Fri
12 11/04/2023 02/05/2023 14 Tue
13 12/04/2023 03/05/2023 15 Wed
14 13/04/2023 05/05/2023 16 Fri
15 14/04/2023 09/05/2023 18 Tue
16 15/04/2023 10/05/2023 19 Wed
17 16/04/2023 12/05/2023 20 Fri
18 25/04/2023 16/05/2023 22 Tue
19 01/05/2023 17/05/2023 23 Wed
20 08/06/2023 19/05/2023 24 Fri
21 10/06/2023 23/05/2023 26 Tue
22 13/06/2023 24/05/2023 27 Wed
23 26/05/2023 28 Fri
24 30/05/2023 30 Tue
25 31/05/2023 31 Wed
26 02/06/2023 32 Fri
27 06/06/2023 34 Tue
28 07/06/2023 35 Wed
29 09/06/2023 36 Fri
30 14/06/2023 37 Wed
31 16/06/2023 38 Fri
32 20/06/2023 40 Tue
33 21/06/2023 41 Wed
34 23/06/2023 42 Fri
35 27/06/2023 44 Tue
36 28/06/2023 45 Wed
37 30/06/2023 46 Fri
38 04/07/2023 48 Tue
39 05/07/2023 49 Wed
40 07/07/2023 50 Fri
41 11/07/2023 52 Tue
Master
Cell Formulas
Range Formula
F2:G101 F2 =LET(d,WORKDAY.INTL(A2-1,SEQUENCE(100),"1001011",D2:D50),xx,XLOOKUP(50,SCAN(0,WEEKDAY(d,2),LAMBDA(a,b,IF(b=2,a+2,a+1))),d,,1),HSTACK(d,SCAN(0,WEEKDAY(d,2),LAMBDA(a,b,IF(b=2,a+2,a+1)))))
H2:H101 H2 =TEXT(INDEX(F2#,,1),"ddd")
B2 B2 =LET(d,WORKDAY.INTL(A2-1,SEQUENCE(100),"1001011",D2:D50),XLOOKUP(50,SCAN(0,WEEKDAY(d,2),LAMBDA(a,b,IF(b=2,a+2,a+1))),d,,1))
B3 B3 =INDEX(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D50),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:D50),2)=2,2,1))>=50),1))
Dynamic array formulas.

Excel Formula:

=ÍNDICE(DIATRABALHO.INTL(A2-1;LIN(A1:A100);"1001011";D2:D50);AGREGAR(15;6;LIN(A1:A100)/(MATRIZ.MULT(--(LIN(A1:A100)>=TRANSPOR(LIN(A1:A100)));SE(DIA.SEMANA(DIATRABALHO.INTL(A2-1;LIN(A1:A100);"1001011";D2:D50);2)=2;2;1))>=50);1))

My bad! It works for 2 hours Tuesday, 1 hour Wednesday and 1 hour Friday.
If i want to change for 2 hours Monday, 1 hour Wednesday and 1 hour Friday. I just change "1001011" to "0101011" right?

Fluff · May 9, 2023

That's right.

But you also need to change this part

Excel Formula:

IF(WEEKDAY(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D50),2)=2,2,1)

from =2 to =1

jgalas · May 9, 2023

Fluff said:
That's right.
But you also need to change this part

Excel Formula:

IF(WEEKDAY(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D50),2)=2,2,1)

from =2 to =1

Thanks a lot. It works
If i want to change for 1 hours Tuesday, 2 hour Wednesday and 1 hour Friday. I keep "1001011", right!
for this example, I have to change something else in the formula IF(WEEKDAY(WORKDAY.INTL(A2-1,ROW(A1:A100),"1001011",D2:D50),2)=2,2,1)?

There can be several combinations, i can have 2/1/1 hours or 1/2/1 or 1/1/2 or 2/2 distributed by days of the week

Fluff · May 10, 2023

jgalas said:
If i want to change for 1 hours Tuesday, 2 hour Wednesday and 1 hour Friday. I keep "1001011", right!

That's right.
For Mon as 2 hours you would need to use =1 instead of =2, for Wed it would be =3 & for Fri =5

jgalas · May 10, 2023

Fluff said:
That's right.
For Mon as 2 hours you would need to use =1 instead of =2, for Wed it would be =3 & for Fri =5

It works. Thanks.

Fluff · May 10, 2023

You're welcome & thanks for the feedback.

Calculate future dates

jgalas

Board Regular

Fluff

MrExcel MVP, Moderator

Excel Facts

jgalas

Board Regular

Fluff

MrExcel MVP, Moderator

jgalas

Board Regular

Fluff

MrExcel MVP, Moderator

jgalas

Board Regular

Fluff

MrExcel MVP, Moderator

jgalas

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Calculate future dates

Board Regular

MrExcel MVP, Moderator

Excel Facts

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock