Convert Chunks of Data to Daily Values

[jknight291]

I have periodic data available throughout the month that I would like to convert to a daily value at the end of the month. I am having a hard time figuring out the logic, though. For instance, I have data available in the format below:

StartDate	StartTime	EndDate	EndTime	Duration
5/3/2021	15:54	5/3/2021	17:48	1.90​
5/4/2021	6:22	5/4/2021	6:51	0.48​
5/6/2021	9:30	5/6/2021	12:21	2.85​
5/6/2021	18:53	5/6/2021	19:39	0.77​
5/12/2021	6:10	5/12/2021	7:36	1.43​
5/13/2021	17:08	5/13/2021	18:00	0.87​
5/18/2021	6:46	5/18/2021	10:07	3.35​
5/18/2021	3:36	5/18/2021	4:33	0.95​
5/18/2021	19:33	5/18/2021	22:18	2.75​
5/19/2021	12:17	5/19/2021	14:10	1.88​
5/20/2021	8:04	5/20/2021	12:11	4.12​
5/25/2021	15:14	5/25/2021	18:04	2.83​
5/26/2021	11:29	5/26/2021	17:39	6.17​
5/28/2021	9:10	5/28/2021	9:33	0.38​
5/20/2021	19:20	5/26/2021	22:33	147.22​
5/26/2021	23:11	5/27/2021	1:16	2.08​
6/7/2021	19:03	6/7/2021	19:45	0.70​
6/8/2021	11:12	6/8/2021	13:06	1.90​

I would like to have another table that lists dates 5/1, 5/2, 5/3, etc. consecutively with each row showing the sum "duration" from above for that data (0 if there was no occurrence from the table above). I used sumifs successfully, but when I have an entry that spans more than 1 day, sumifs doesn't work.

Does anyone have any ideas?

 

[DanteAmor]

You have 147.22 from the date May 20 through May 26.
How do you want to see or where do you want to put those 147.22?

Dante Amor.xlsm
	A	B	C	D	E	F	G	H
1	StartDate	StartTime	EndDate	EndTime	Duration		Date	Duration
2	03/05/2021	15:54	03/05/2021	17:48	1.9		01/05/2021	0
3	04/05/2021	06:22	04/05/2021	06:51	0.48		02/05/2021	0
4	06/05/2021	09:30	06/05/2021	12:21	2.85		03/05/2021	1.9
5	06/05/2021	18:53	06/05/2021	19:39	0.77		04/05/2021	0.48
6	12/05/2021	06:10	12/05/2021	07:36	1.43		05/05/2021	0
7	13/05/2021	17:08	13/05/2021	18:00	0.87		06/05/2021	3.62
8	18/05/2021	06:46	18/05/2021	10:07	3.35		07/05/2021	0
9	18/05/2021	03:36	18/05/2021	04:33	0.95		08/05/2021	0
10	18/05/2021	19:33	18/05/2021	22:18	2.75		09/05/2021	0
11	19/05/2021	12:17	19/05/2021	14:10	1.88		10/05/2021	0
12	20/05/2021	08:04	20/05/2021	12:11	4.12		11/05/2021	0
13	25/05/2021	15:14	25/05/2021	18:04	2.83		12/05/2021	1.43
14	26/05/2021	11:29	26/05/2021	17:39	6.17		13/05/2021	0.87
15	28/05/2021	09:10	28/05/2021	09:33	0.38		14/05/2021	0
16	20/05/2021	19:20	26/05/2021	22:33	147.22		15/05/2021	0
17	26/05/2021	23:11	27/05/2021	01:16	2.08		16/05/2021	0
18	07/06/2021	19:03	07/06/2021	19:45	0.7		17/05/2021	0
19	08/06/2021	11:12	08/06/2021	13:06	1.9		18/05/2021	7.05
20							19/05/2021	1.88
21							20/05/2021	151.34
22							21/05/2021	147.22
23							22/05/2021	147.22
24							23/05/2021	147.22
25							24/05/2021	147.22
26							25/05/2021	150.05
27							26/05/2021	155.47
28							27/05/2021	2.08
Dante Amor

Cell Formulas
Range	Formula
H2:H28	H2	=SUMIFS($E$2:$E$19,$A$2:$A$19,"<="&G2,$C$2:$C$19,">="&G2)

Note: To put an example here use XL2BB tool.

 

[jknight291]

The data is hours, so for entries with multiple days, would like to see "24" for complete days within the range.

 

[DanteAmor]

I don't understand, could you explain it with the same example?

 

[jknight291]

Sure.

For May 20 through May 26, I would like to see the following:

May 20 4.65
May 21 24
May 22 24
May 23 24
May 24 24
May 25 24
May 26 23.5
May 27 1.27

 

[DanteAmor]

Check out the following, I even put a sum at the end to check the result.

Dante Amor
	A	B	C	D	E	F	G	H
1	StartDate	StartTime	EndDate	EndTime	Duration		Date	Duration
2	03/05/2021	15:54	03/05/2021	17:48	1.9		01/05/2021	0.00
3	04/05/2021	06:22	04/05/2021	06:51	0.48		02/05/2021	0.00
4	06/05/2021	09:30	06/05/2021	12:21	2.85		03/05/2021	1.90
5	06/05/2021	18:53	06/05/2021	19:39	0.77		04/05/2021	0.48
6	12/05/2021	06:10	12/05/2021	07:36	1.43		05/05/2021	0.00
7	13/05/2021	17:08	13/05/2021	18:00	0.87		06/05/2021	3.62
8	18/05/2021	06:46	18/05/2021	10:07	3.35		07/05/2021	0.00
9	18/05/2021	03:36	18/05/2021	04:33	0.95		08/05/2021	0.00
10	18/05/2021	19:33	18/05/2021	22:18	2.75		09/05/2021	0.00
11	19/05/2021	12:17	19/05/2021	14:10	1.88		10/05/2021	0.00
12	20/05/2021	08:04	20/05/2021	12:11	4.12		11/05/2021	0.00
13	25/05/2021	15:14	25/05/2021	18:04	2.83		12/05/2021	1.43
14	26/05/2021	11:29	26/05/2021	17:39	6.17		13/05/2021	0.87
15	28/05/2021	09:10	28/05/2021	09:33	0.38		14/05/2021	0.00
16	20/05/2021	19:20	26/05/2021	22:33	147.22		15/05/2021	0.00
17	26/05/2021	23:11	27/05/2021	01:16	2.08		16/05/2021	0.00
18	07/06/2021	19:03	07/06/2021	19:45	0.7		17/05/2021	0.00
19	08/06/2021	11:12	08/06/2021	13:06	1.9		18/05/2021	7.05
20					182.63		19/05/2021	1.88
21							20/05/2021	8.79
22							21/05/2021	24.00
23							22/05/2021	24.00
24							23/05/2021	24.00
25							24/05/2021	24.00
26							25/05/2021	26.83
27							26/05/2021	29.54
28							27/05/2021	1.27
29							28/05/2021	0.38
30							07/06/2021	0.70
31							08/06/2021	1.90
32								182.63
Dante Amor

Cell Formulas
Range	Formula
E20	E20	=SUM(E2:E19)
H2:H31	H2	=SUM(IF(($A$2:$A$19=G2)*($C$2:$C$19=G2),$E$2:$E$19, IF(($A$2:$A$19<G2)*($C$2:$C$19>G2),24, IF(($A$2:$A$19=G2)*($C$2:$C$19>G2),(($A$2:$A$19+1)-($A$2:$A$19+$B$2:$B$19))*24, IF(($A$2:$A$19<G2)*($C$2:$C$19=G2),(($C$2:$C$19+$D$2:$D$19)-($C$2:$C$19))*24)))))
H32	H32	=SUM(H2:H31)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jknight291]

Wow thank you. I wrote a VBA program with essentially the same logic and a for loop to progress this date but this worked nicely too and is much faster.

Thank you!

 

[DanteAmor]

If you have performance problems with your sheet because they are array formulas, try the following VBA:

Sub CalculateTime()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  Dim dic As Object
  Dim minDate As Date, maxDate As Date
  
  a = Range("A2", Range("E" & Rows.Count).End(3)).Value2
  b = Range("G2", Range("E" & Rows.Count).End(3)).Value2
  minDate = WorksheetFunction.Min(Range("A2", Range("A" & Rows.Count).End(3)))
  maxDate = WorksheetFunction.Max(Range("C2", Range("C" & Rows.Count).End(3)))
  
  Set dic = CreateObject("Scripting.Dictionary")
  For i = minDate To maxDate
    dic(i) = Empty
  Next
  
  For i = 1 To UBound(a)
    For j = a(i, 1) To a(i, 3)
      Select Case True
        Case a(i, 1) = j And a(i, 3) = j
          dic(j) = dic(j) + a(i, 5)
        Case a(i, 1) < j And a(i, 3) > j
          dic(j) = dic(j) + 24
        Case a(i, 1) = j And a(i, 3) > j
          dic(j) = dic(j) + (((a(i, 1) + 1) - (a(i, 1) + a(i, 2))) * 24)
        Case a(i, 1) < j And a(i, 3) = j
          dic(j) = dic(j) + (((a(i, 3) + a(i, 4)) - a(i, 3)) * 24)
      End Select
    Next
  Next
  'Results in G2 down
  Range("G2").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
End Sub