SOLVED

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

Copper Contributor

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 empty cell on blue table. I hope that it just use 1 general formula and can be draged down.

I don't have any idea to combine some fomulas to solve that problem. Can someone is helping me out on this?
I'm using excel 2013.

 

Really appreciate your answer.
Thank you.

11 Replies

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

Screenshot_2022-12-15-18-18-29-826_cn.uujian.browser.jpg

Screenshot_2022-12-15-18-11-50-274_cn.uujian.browser.jpg

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 

In G12:

 

=IF(INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11)="","",ROUNDUP(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),0))

 

Format as General.

Fill to the right, and down if Excel doesn't do that automatically.

Thank you for the solution, apreciate it.

But I need precision calculation results of each date duration in table1 (blue).

e.g the duration is:
12/10/2022 16.10 to 09/12/2022 10.00
so the result should be 6.417 days on 1st week December.

Can you help me with this problem?

best response confirmed by wahidfajar (Copper Contributor)
Solution

@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))

Wow its works perfectly. You rock man! Thank you

I'll need more help on excel, may be I'll catch you again in this post later. Again, thank you sir!

Hi @Hans Vogelaar 

I have the further problem with date calculation.

Now, I need a formula to calculate only days passed on 2022 on all completed activity. In this problem I don't have any other table but only this 1.

Would you mind helping with this problem?
Thank you

 

@wahidfajar 

That would be

 

=MAX(MIN([@Finish],DATE(2023,1,1))-MAX([@Start],DATE(2022,1,1)),0)

waw thank you. I still haven't mastered sql but I'll try since you gave an example

Hi @Hans Vogelaar 

I have next problem with summarize days between 2 formula you solve before this. (The file is attached).

 

So, there are innumerable durations of each dates on blue table based on cutoff in the yellow table.
I need the result between cummulative days and cummulative on every week to produce the same result.

 

Can you help me for this?

this is my thinking I describe in sql.

re:
cummulative days and cummulative on every week to produce the same result.

need to clear
March 5 A 2022/1/15 13:00 2022/3/9 17:00

row 7

2022/1/15 13:00 2022/3/9 17:00
cross Jan Feb and Mar 3 months?
is not it right to need split one row to 3 rows for holding this activity A?

@wahidfajar 

Column G (Full Cumvmulative Days) counts the total number of days for the entire year 2022, not just for the month in column B (Month).

The numbers in columns H to L (Com Week 1 to Com Week 5) are for the weeks of the month in column B only. So their sum is much less than that of column G.

Would you prefer the numbers in column G to be for the specified month only, instead of for the entire year?

1 best response

Accepted Solutions
best response confirmed by wahidfajar (Copper Contributor)
Solution

@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))

View solution in original post