Forum Discussion

wahidfajar's avatar
wahidfajar
Copper Contributor
Dec 15, 2022
Solved

Calculate days elapsed between 2 duration dates based on 2 cut off dates on a month

Hi, I'm working on calculating days between date duration based on 2 cutoff dates on a specific month. I just need the days between duration and cut off based on month in row $B$ to be filled on e...
admin
excel
Formulas and Functions
Office Scripts
  • HansVogelaar's avatar
    HansVogelaar
    Dec 15, 2022

    wahidfajar 

    =IF(INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11)="","",MAX(MIN(Table1[@[Finish]:[Finish]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11))-MAX(Table1[@[Start]:[Start]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-12)),0))

peiyezhu's avatar
peiyezhu
Bronze Contributor
Dec 15, 2022

do you mean to expand activit all days and match days between open and closed?

 

 

id Activity Open days workday

1 A 2021-10-26 0 2021-10-26

1 A 2021-10-26 1 2021-10-27

1 A 2021-10-26 2 2021-10-28

1 A 2021-10-26 3 2021-10-29

1 A 2021-10-26 4 2021-10-30

1 A 2021-10-26 5 2021-10-31

1 A 2021-10-26 6 2021-11-01

1 A 2021-10-26 7 2021-11-02

1 A 2021-10-26 8 2021-11-03

1 A 2021-10-26 9 2021-11-04

1 A 2021-10-26 10 2021-11-05

1 A 2021-10-26 11 2021-11-06

1 A 2021-10-26 12 2021-11-07

1 A 2021-10-26 13 2021-11-08

1 A 2021-10-26 14 2021-11-09

1 A 2021-10-26 15 2021-11-10

1 A 2021-10-26 16 2021-11-11

1 A 2021-10-26 17 2021-11-12

1 A 2021-10-26 18 2021-11-13

1 A 2021-10-26 19 2021-11-14

1 A 2021-10-26 20 2021-11-15

1 A 2021-10-26 21 2021-11-16

1 A 2021-10-26 22 2021-11-17

1 A 2021-10-26 23 2021-11-18

1 A 2021-10-26 24 2021-11-19

1 A 2021-10-26 25 2021-11-20

1 A 2021-10-26 26 2021-11-21

1 A 2021-10-26 27 2021-11-22

1 A 2021-10-26 28 2021-11-23

1 A 2021-10-26 29 2021-11-24

1 A 2021-10-26 30 2021-11-25

1 A 2021-10-26 31 2021-11-26

1 A 2021-10-26 32 2021-11-27

1 A 2021-10-26 33 2021-11-28

1 A 2021-10-26 34 2021-11-29

1 A 2021-10-26 35 2021-11-30

1 A 2021-10-26 36 2021-12-01

1 A 2021-10-26 37 2021-12-02

1 A 2021-10-26 38 2021-12-03

1 A 2021-10-26 39 2021-12-04

1 A 2021-10-26 40 2021-12-05

1 A 2021-10-26 41 2021-12-06

1 A 2021-10-26 42 2021-12-07

1 A 2021-10-26 43 2021-12-08

1 A 2021-10-26 44 2021-12-09

1 A 2021-10-26 45 2021-12-10

1 A 2021-10-26 46 2021-12-11

1 A 2021-10-26 47 2021-12-12

1 A 2021-10-26 48 2021-12-13

1 A 2021-10-26 49 2021-12-14

1 A 2021-10-26 50 2021-12-15

1 A 2021-10-26 51 2021-12-16

1 A 2021-10-26 52 2021-12-17

1 A 2021-10-26 53 2021-12-18

1 A 2021-10-26 54 2021-12-19

1 A 2021-10-26 55 2021-12-20

1 A 2021-10-26 56 2021-12-21

1 A 2021-10-26 57 2021-12-22

1 A 2021-10-26 58 2021-12-23

1 A 2021-10-26 59 2021-12-24

1 A 2021-10-26 60 2021-12-25

1 A 2021-10-26 61 2021-12-26

1 A 2021-10-26 62 2021-12-27

1 A 2021-10-26 63 2021-12-28

1 A 2021-10-26 64 2021-12-29

1 A 2021-10-26 65 2021-12-30

1 A 2021-10-26 66 2021-12-31

1 A 2021-10-26 67 2022-01-01

1 A 2021-10-26 68 2022-01-02

1 A 2021-10-26 69 2022-01-03

1 A 2021-10-26 70 2022-01-04

1 A 2021-10-26 71 2022-01-05

1 A 2021-10-26 72 2022-01-06

1 A 2021-10-26 73 2022-01-07

1 A 2021-10-26 74 2022-01-08

1 A 2021-10-26 75 2022-01-09

1 A 2021-10-26 76 2022-01-10

1 A 2021-10-26 77 2022-01-11

1 A 2021-10-26 78 2022-01-12

1 A 2021-10-26 79 2022-01-13

1 A 2021-10-26 80 2022-01-14

1 A 2021-10-26 81 2022-01-15

1 A 2021-10-26 82 2022-01-16

1 A 2021-10-26 83 2022-01-17

1 A 2021-10-26 84 2022-01-18

1 A 2021-10-26 85 2022-01-19

1 A 2021-10-26 86 2022-01-20

1 A 2021-10-26 87 2022-01-21

1 A 2021-10-26 88 2022-01-22

1 A 2021-10-26 89 2022-01-23

1 A 2021-10-26 90 2022-01-24

1 A 2021-10-26 91 2022-01-25

1 A 2021-10-26 92 2022-01-26

1 A 2021-10-26 93 2022-01-27

1 A 2021-10-26 94 2022-01-28

1 A 2021-10-26 95 2022-01-29

1 A 2021-10-26 96 2022-01-30

1 A 2021-10-26 97 2022-01-31

1 A 2021-10-26 98 2022-02-01

1 A 2021-10-26 99 2022-02-02

1 A 2021-10-26 100 2022-02-03

1 A 2021-10-26 101 2022-02-04

1 A 2021-10-26 102 2022-02-05

1 A 2021-10-26 103 2022-02-06

1 A 2021-10-26 104 2022-02-07

1 A 2021-10-26 105 2022-02-08

1 A 2021-10-26 106 2022-02-09

1 A 2021-10-26 107 2022-02-10

1 A 2021-10-26 108 2022-02-11

1 A 2021-10-26 109 2022-02-12

1 A 2021-10-26 110 2022-02-13

1 A 2021-10-26 111 2022-02-14

1 A 2021-10-26 112 2022-02-15

1 A 2021-10-26 113 2022-02-16

1 A 2021-10-26 114 2022-02-17

1 A 2021-10-26 115 2022-02-18

1 A 2021-10-26 116 2022-02-19

1 A 2021-10-26 117 2022-02-20

1 A 2021-10-26 118 2022-02-21

1 A 2021-10-26 119 2022-02-22

1 A 2021-10-26 120 2022-02-23

1 A 2021-10-26 121 2022-02-24

1 A 2021-10-26 122 2022-02-25

1 A 2021-10-26 123 2022-02-26

1 A 2021-10-26 124 2022-02-27

1 A 2021-10-26 125 2022-02-28

1 A 2021-10-26 126 2022-03-01

1 A 2021-10-26 127 2022-03-02

1 A 2021-10-26 128 2022-03-03

1 A 2021-10-26 129 2022-03-04

1 A 2021-10-26 130 2022-03-05

1 A 2021-10-26 131 2022-03-06

1 A 2021-10-26 132 2022-03-07

1 A 2021-10-26 133 2022-03-08

1 A 2021-10-26 134 2022-03-09

1 A 2021-10-26 135 2022-03-10

1 A 2021-10-26 136 2022-03-11

1 A 2021-10-26 137 2022-03-12

1 A 2021-10-26 138 2022-03-13

1 A 2021-10-26 139 2022-03-14

1 A 2021-10-26 140 2022-03-15

1 A 2021-10-26 141 2022-03-16

1 A 2021-10-26 142 2022-03-17

1 A 2021-10-26 143 2022-03-18

1 A 2021-10-26 144 2022-03-19

1 A 2021-10-26 145 2022-03-20

1 A 2021-10-26 146 2022-03-21

1 A 2021-10-26 147 2022-03-22

1 A 2021-10-26 148 2022-03-23

1 A 2021-10-26 149 2022-03-24

1 A 2021-10-26 150 2022-03-25

1 A 2021-10-26 151 2022-03-26

1 A 2021-10-26 152 2022-03-27

1 A 2021-10-26 153 2022-03-28

1 A 2021-10-26 154 2022-03-29

1 A 2021-10-26 155 2022-03-30

2 B 2021-10-05 0 2021-10-05

2 B 2021-10-05 1 2021-10-06

2 B 2021-10-05 2 2021-10-07

2 B 2021-10-05 3 2021-10-08

2 B 2021-10-05 4 2021-10-09

2 B 2021-10-05 5 2021-10-10

2 B 2021-10-05 6 2021-10-11

2 B 2021-10-05 7 2021-10-12

2 B 2021-10-05 8 2021-10-13

2 B 2021-10-05 9 2021-10-14

2 B 2021-10-05 10 2021-10-15

2 B 2021-10-05 11 2021-10-16

2 B 2021-10-05 12 2021-10-17

2 B 2021-10-05 13 2021-10-18

2 B 2021-10-05 14 2021-10-19

2 B 2021-10-05 15 2021-10-20

2 B 2021-10-05 16 2021-10-21

2 B 2021-10-05 17 2021-10-22

2 B 2021-10-05 18 2021-10-23

2 B 2021-10-05 19 2021-10-24

2 B 2021-10-05 20 2021-10-25

2 B 2021-10-05 21 2021-10-26

2 B 2021-10-05 22 2021-10-27

2 B 2021-10-05 23 2021-10-28

2 B 2021-10-05 24 2021-10-29

2 B 2021-10-05 25 2021-10-30

2 B 2021-10-05 26 2021-10-31

2 B 2021-10-05 27 2021-11-01

2 B 2021-10-05 28 2021-11-02

2 B 2021-10-05 29 2021-11-03

2 B 2021-10-05 30 2021-11-04

2 B 2021-10-05 31 2021-11-05

2 B 2021-10-05 32 2021-11-06

2 B 2021-10-05 33 2021-11-07

2 B 2021-10-05 34 2021-11-08

2 B 2021-10-05 35 2021-11-09

2 B 2021-10-05 36 2021-11-10

2 B 2021-10-05 37 2021-11-11

2 B 2021-10-05 38 2021-11-12

2 B 2021-10-05 39 2021-11-13

2 B 2021-10-05 40 2021-11-14

2 B 2021-10-05 41 2021-11-15

2 B 2021-10-05 42 2021-11-16

2 B 2021-10-05 43 2021-11-17

id old_rowid Open 上层属性 days workday

1 1 2022-01-01 1 0 2022-01-01

1 1 2022-01-01 1 1 2022-01-02

1 1 2022-01-01 1 2 2022-01-03

1 1 2022-01-01 1 3 2022-01-04

1 1 2022-01-01 1 4 2022-01-05

1 1 2022-01-01 1 5 2022-01-06

1 1 2022-01-01 1 6 2022-01-07

2 1 2022-01-08 2 0 2022-01-08

2 1 2022-01-08 2 1 2022-01-09

2 1 2022-01-08 2 2 2022-01-10

2 1 2022-01-08 2 3 2022-01-11

2 1 2022-01-08 2 4 2022-01-12

2 1 2022-01-08 2 5 2022-01-13

2 1 2022-01-08 2 6 2022-01-14

3 1 2022-01-15 3 0 2022-01-15

3 1 2022-01-15 3 1 2022-01-16

3 1 2022-01-15 3 2 2022-01-17

3 1 2022-01-15 3 3 2022-01-18

3 1 2022-01-15 3 4 2022-01-19

3 1 2022-01-15 3 5 2022-01-20

3 1 2022-01-15 3 6 2022-01-21

4 1 2022-01-22 4 0 2022-01-22

4 1 2022-01-22 4 1 2022-01-23

4 1 2022-01-22 4 2 2022-01-24

4 1 2022-01-22 4 3 2022-01-25

4 1 2022-01-22 4 4 2022-01-26

4 1 2022-01-22 4 5 2022-01-27

4 1 2022-01-22 4 6 2022-01-28

5 2 2022-01-29 1 0 2022-01-29

5 2 2022-01-29 1 1 2022-01-30

5 2 2022-01-29 1 2 2022-01-31

5 2 2022-01-29 1 3 2022-02-01

5 2 2022-01-29 1 4 2022-02-02

5 2 2022-01-29 1 5 2022-02-03

5 2 2022-01-29 1 6 2022-02-04

6 2 2022-02-05 2 0 2022-02-05

6 2 2022-02-05 2 1 2022-02-06

6 2 2022-02-05 2 2 2022-02-07

6 2 2022-02-05 2 3 2022-02-08

6 2 2022-02-05 2 4 2022-02-09

6 2 2022-02-05 2 5 2022-02-10

6 2 2022-02-05 2 6 2022-02-11

7 2 2022-02-12 3 0 2022-02-12

7 2 2022-02-12 3 1 2022-02-13

7 2 2022-02-12 3 2 2022-02-14

7 2 2022-02-12 3 3 2022-02-15

7 2 2022-02-12 3 4 2022-02-16

7 2 2022-02-12 3 5 2022-02-17

7 2 2022-02-12 3 6 2022-02-18

8 2 2022-02-19 4 0 2022-02-19

8 2 2022-02-19 4 1 2022-02-20

8 2 2022-02-19 4 2 2022-02-21

8 2 2022-02-19 4 3 2022-02-22

8 2 2022-02-19 4 4 2022-02-23

8 2 2022-02-19 4 5 2022-02-24

8 2 2022-02-19 4 6 2022-02-25

9 3 2022-02-26 1 0 2022-02-26

9 3 2022-02-26 1 1 2022-02-27

9 3 2022-02-26 1 2 2022-02-28

9 3 2022-02-26 1 3 2022-03-01

9 3 2022-02-26 1 4 2022-03-02

9 3 2022-02-26 1 5 2022-03-03

9 3 2022-02-26 1 6 2022-03-04

10 3 2022-03-05 2 0 2022-03-05

10 3 2022-03-05 2 1 2022-03-06

10 3 2022-03-05 2 2 2022-03-07

10 3 2022-03-05 2 3 2022-03-08

10 3 2022-03-05 2 4 2022-03-09

10 3 2022-03-05 2 5 2022-03-10

10 3 2022-03-05 2 6 2022-03-11

11 3 2022-03-12 3 0 2022-03-12

11 3 2022-03-12 3 1 2022-03-13

11 3 2022-03-12 3 2 2022-03-14

11 3 2022-03-12 3 3 2022-03-15

11 3 2022-03-12 3 4 2022-03-16

11 3 2022-03-12 3 5 2022-03-17

11 3 2022-03-12 3 6 2022-03-18

12 3 2022-03-19 4 0 2022-03-19

12 3 2022-03-19 4 1 2022-03-20

12 3 2022-03-19 4 2 2022-03-21

12 3 2022-03-19 4 3 2022-03-22

12 3 2022-03-19 4 4 2022-03-23

12 3 2022-03-19 4 5 2022-03-24

12 3 2022-03-19 4 6 2022-03-25

13 3 2022-03-26 5 0 2022-03-26

13 3 2022-03-26 5 1 2022-03-27

13 3 2022-03-26 5 2 2022-03-28

13 3 2022-03-26 5 3 2022-03-29

13 3 2022-03-26 5 4 2022-03-30

13 3 2022-03-26 5 5 2022-03-31

13 3 2022-03-26 5 6 2022-04-01

14 4 2022-04-02 1 0 2022-04-02

14 4 2022-04-02 1 1 2022-04-03

14 4 2022-04-02 1 2 2022-04-04

14 4 2022-04-02 1 3 2022-04-05

14 4 2022-04-02 1 4 2022-04-06

14 4 2022-04-02 1 5 2022-04-07

14 4 2022-04-02 1 6 2022-04-08

15 4 2022-04-09 2 0 2022-04-09

15 4 2022-04-09 2 1 2022-04-10

15 4 2022-04-09 2 2 2022-04-11

15 4 2022-04-09 2 3 2022-04-12

15 4 2022-04-09 2 4 2022-04-13

15 4 2022-04-09 2 5 2022-04-14

15 4 2022-04-09 2 6 2022-04-15

16 4 2022-04-16 3 0 2022-04-16

16 4 2022-04-16 3 1 2022-04-17

16 4 2022-04-16 3 2 2022-04-18

16 4 2022-04-16 3 3 2022-04-19

16 4 2022-04-16 3 4 2022-04-20

16 4 2022-04-16 3 5 2022-04-21

16 4 2022-04-16 3 6 2022-04-22

17 4 2022-04-23 4 0 2022-04-23

17 4 2022-04-23 4 1 2022-04-24

17 4 2022-04-23 4 2 2022-04-25

17 4 2022-04-23 4 3 2022-04-26

17 4 2022-04-23 4 4 2022-04-27

17 4 2022-04-23 4 5 2022-04-28

17 4 2022-04-23 4 6 2022-04-29

18 5 2022-04-30 1 0 2022-04-30

18 5 2022-04-30 1 1 2022-05-01

18 5 2022-04-30 1 2 2022-05-02

18 5 2022-04-30 1 3 2022-05-03

18 5 2022-04-30 1 4 2022-05-04

18 5 2022-04-30 1 5 2022-05-05

18 5 2022-04-30 1 6 2022-05-06

19 5 2022-05-07 2 0 2022-05-07

19 5 2022-05-07 2 1 2022-05-08

19 5 2022-05-07 2 2 2022-05-09

19 5 2022-05-07 2 3 2022-05-10

19 5 2022-05-07 2 4 2022-05-11

19 5 2022-05-07 2 5 2022-05-12

19 5 2022-05-07 2 6 2022-05-13

20 5 2022-05-14 3 0 2022-05-14

20 5 2022-05-14 3 1 2022-05-15

20 5 2022-05-14 3 2 2022-05-16

20 5 2022-05-14 3 3 2022-05-17

20 5 2022-05-14 3 4 2022-05-18

20 5 2022-05-14 3 5 2022-05-19

20 5 2022-05-14 3 6 2022-05-20

21 5 2022-05-21 4 0 2022-05-21

21 5 2022-05-21 4 1 2022-05-22

21 5 2022-05-21 4 2 2022-05-23

21 5 2022-05-21 4 3 2022-05-24

21 5 2022-05-21 4 4 2022-05-25

21 5 2022-05-21 4 5 2022-05-26

21 5 2022-05-21 4 6 2022-05-27

22 6 2022-05-28 1 0 2022-05-28

22 6 2022-05-28 1 1 2022-05-29

22 6 2022-05-28 1 2 2022-05-30

22 6 2022-05-28 1 3 2022-05-31

22 6 2022-05-28 1 4 2022-06-01

22 6 2022-05-28 1 5 2022-06-02

22 6 2022-05-28 1 6 2022-06-03

23 6 2022-06-04 2 0 2022-06-04

23 6 2022-06-04 2 1 2022-06-05

23 6 2022-06-04 2 2 2022-06-06

23 6 2022-06-04 2 3 2022-06-07

23 6 2022-06-04 2 4 2022-06-08

23 6 2022-06-04 2 5 2022-06-09

23 6 2022-06-04 2 6 2022-06-10

24 6 2022-06-11 3 0 2022-06-11

24 6 2022-06-11 3 1 2022-06-12

24 6 2022-06-11 3 2 2022-06-13

24 6 2022-06-11 3 3 2022-06-14

24 6 2022-06-11 3 4 2022-06-15

24 6 2022-06-11 3 5 2022-06-16

24 6 2022-06-11 3 6 2022-06-17

25 6 2022-06-18 4 0 2022-06-18

25 6 2022-06-18 4 1 2022-06-19

25 6 2022-06-18 4 2 2022-06-20

25 6 2022-06-18 4 3 2022-06-21

25 6 2022-06-18 4 4 2022-06-22

25 6 2022-06-18 4 5 2022-06-23

25 6 2022-06-18 4 6 2022-06-24

26 6 2022-06-25 5 0 2022-06-25

26 6 2022-06-25 5 1 2022-06-26

26 6 2022-06-25 5 2 2022-06-27

26 6 2022-06-25 5 3 2022-06-28

26 6 2022-06-25 5 4 2022-06-29

26 6 2022-06-25 5 5 2022-06-30

26 6 2022-06-25 5 6 2022-07-01

27 7 2022-07-02 1 0 2022-07-02

27 7 2022-07-02 1 1 2022-07-03

27 7 2022-07-02 1 2 2022-07-04

27 7 2022-07-02 1 3 2022-07-05

27 7 2022-07-02 1 4 2022-07-06

27 7 2022-07-02 1 5 2022-07-07

27 7 2022-07-02 1 6 2022-07-08

28 7 2022-07-09 2 0 2022-07-09

28 7 2022-07-09 2 1 2022-07-10

28 7 2022-07-09 2 2 2022-07-11

28 7 2022-07-09 2 3 2022-07-12

28 7 2022-07-09 2 4 2022-07-13

28 7 2022-07-09 2 5 2022-07-14

28 7 2022-07-09 2 6 2022-07-15

29 7 2022-07-16 3 0 2022-07-16

29 7 2022-07-16 3 1 2022-07-17

29 7 2022-07-16 3 2 2022-07-18

29 7 2022-07-16 3 3 2022-07-19

Activity month week1 week2 week3 week4 week5

A 1 7 7 7 7 

A 2 7 7 7 7 

A 3 7 7 7 7 5

B 1 7 7 4  

C 2 7 7 7 

C 3 7 7 7 7 7

C 4 7 7 3  

D 10 3 7 7 7 

D 11 7 7 7 7 7

D 12 7 3 7

 

 

create temp table aa as 
select  rowid old_rowid,* from calculating_days_between_date_duration;
cli_unstack~aa~Open,Close;
create temp table bb as 
select old_rowid,Open,上层属性,'0-'||((udf_strtotime(close)-udf_strtotime(Open))/60/60/24) days from aa_unstack order by old_rowid,上层属性;
create temp table cc as 
select old_rowid,Open,上层属性,split_num(days) days from bb;
cli_split_data~cc~,~days;
create temp table dd as 
select *,date(Open,'+'||days||' days') 
 workday from ccsplit;

create temp table aaa as 
select  Activity,Start Open,Finish Close from activity_start_finish;

create temp table bbb as 
select Activity, Open,'0-'||((udf_strtotime(close)-udf_strtotime(Open))/60/60/24) days from aaa  order by Activity;
create temp table ccc as 
select Activity,Open,split_num(days) days from bbb;
cli_split_data~ccc~,~days;
create temp table ddd as 
select *,date(Open,'+'||days||' days') 
 workday from cccsplit;
select * from ddd limit 200;
select * from dd limit 200;
create temp table aaaa as 
select Activity,old_rowid,上层属性, workday from ddd join dd using(workday);
create temp table bbbb as 
select Activity,old_rowid month,'week'||上层属性 
week ,count(*) c_wkdays from aaaa group by Activity,old_rowid,上层属性;
cli_create_two_dim~bbbb~week~c_wkdays;
select * from bbbb_two_dim;

if so,some sql for your reference.

  • wahidfajar's avatar
    wahidfajar
    Copper Contributor
    Dec 16, 2022
    waw thank you. I still haven't mastered sql but I'll try since you gave an example

Resources