Dec 14 2022 08:34 PM - edited Dec 14 2022 08:45 PM
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.
Dec 15 2022 02:23 AM - edited Dec 15 2022 02:28 AM
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.
Dec 15 2022 05:48 AM
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.
Dec 15 2022 06:33 AM - edited Dec 15 2022 06:36 AM
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?
Dec 15 2022 06:52 AM
Solution=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))
Dec 15 2022 07:04 AM
Dec 15 2022 08:58 AM
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
Dec 15 2022 12:22 PM
Dec 15 2022 07:04 PM
Dec 15 2022 07:22 PM
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?
Dec 15 2022 08:04 PM - edited Dec 15 2022 08:09 PM
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?
Dec 16 2022 04:53 AM
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?
Dec 15 2022 06:52 AM
Solution=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))