# Convert Chunks of Data to Daily Values

#### jknight291

##### New Member
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?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### DanteAmor

##### Well-known Member
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
ABCDEFGH
1StartDateStartTimeEndDateEndTimeDurationDateDuration
203/05/202115:5403/05/202117:481.901/05/20210
304/05/202106:2204/05/202106:510.4802/05/20210
406/05/202109:3006/05/202112:212.8503/05/20211.9
506/05/202118:5306/05/202119:390.7704/05/20210.48
612/05/202106:1012/05/202107:361.4305/05/20210
713/05/202117:0813/05/202118:000.8706/05/20213.62
818/05/202106:4618/05/202110:073.3507/05/20210
918/05/202103:3618/05/202104:330.9508/05/20210
1018/05/202119:3318/05/202122:182.7509/05/20210
1119/05/202112:1719/05/202114:101.8810/05/20210
1220/05/202108:0420/05/202112:114.1211/05/20210
1325/05/202115:1425/05/202118:042.8312/05/20211.43
1426/05/202111:2926/05/202117:396.1713/05/20210.87
1528/05/202109:1028/05/202109:330.3814/05/20210
1620/05/202119:2026/05/202122:33147.2215/05/20210
1726/05/202123:1127/05/202101:162.0816/05/20210
1807/06/202119:0307/06/202119:450.717/05/20210
1908/06/202111:1208/06/202113:061.918/05/20217.05
2019/05/20211.88
2120/05/2021151.34
2221/05/2021147.22
2322/05/2021147.22
2423/05/2021147.22
2524/05/2021147.22
2625/05/2021150.05
2726/05/2021155.47
2827/05/20212.08
Dante Amor
Cell Formulas
RangeFormula
H2:H28H2=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

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

#### DanteAmor

##### Well-known Member
I don't understand, could you explain it with the same example?

#### jknight291

##### New Member
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

##### Well-known Member
Check out the following, I even put a sum at the end to check the result.

Dante Amor
ABCDEFGH
1StartDateStartTimeEndDateEndTimeDurationDateDuration
203/05/202115:5403/05/202117:481.901/05/20210.00
304/05/202106:2204/05/202106:510.4802/05/20210.00
406/05/202109:3006/05/202112:212.8503/05/20211.90
506/05/202118:5306/05/202119:390.7704/05/20210.48
612/05/202106:1012/05/202107:361.4305/05/20210.00
713/05/202117:0813/05/202118:000.8706/05/20213.62
818/05/202106:4618/05/202110:073.3507/05/20210.00
918/05/202103:3618/05/202104:330.9508/05/20210.00
1018/05/202119:3318/05/202122:182.7509/05/20210.00
1119/05/202112:1719/05/202114:101.8810/05/20210.00
1220/05/202108:0420/05/202112:114.1211/05/20210.00
1325/05/202115:1425/05/202118:042.8312/05/20211.43
1426/05/202111:2926/05/202117:396.1713/05/20210.87
1528/05/202109:1028/05/202109:330.3814/05/20210.00
1620/05/202119:2026/05/202122:33147.2215/05/20210.00
1726/05/202123:1127/05/202101:162.0816/05/20210.00
1807/06/202119:0307/06/202119:450.717/05/20210.00
1908/06/202111:1208/06/202113:061.918/05/20217.05
20182.6319/05/20211.88
2120/05/20218.79
2221/05/202124.00
2322/05/202124.00
2423/05/202124.00
2524/05/202124.00
2625/05/202126.83
2726/05/202129.54
2827/05/20211.27
2928/05/20210.38
3007/06/20210.70
3108/06/20211.90
32182.63
Dante Amor
Cell Formulas
RangeFormula
E20E20=SUM(E2:E19)
H2:H31H2=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)))))
H32H32=SUM(H2:H31)
Press CTRL+SHIFT+ENTER to enter array formulas.

#### jknight291

##### New Member
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

##### Well-known Member
If you have performance problems with your sheet because they are array formulas, try the following VBA:

VBA Code:
``````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``````

Replies
1
Views
24
Replies
1
Views
275
Replies
2
Views
109
Replies
3
Views
82
Replies
3
Views
219

1,181,892
Messages
5,932,668
Members
436,850
Latest member
Jasperlee93

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back