Convert Chunks of Data to Daily Values

jknight291

New Member
Joined
Jun 6, 2017
Messages
7
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:

StartDateStartTimeEndDateEndTimeDuration
5/3/202115:545/3/202117:48
1.90​
5/4/20216:225/4/20216:51
0.48​
5/6/20219:305/6/202112:21
2.85​
5/6/202118:535/6/202119:39
0.77​
5/12/20216:105/12/20217:36
1.43​
5/13/202117:085/13/202118:00
0.87​
5/18/20216:465/18/202110:07
3.35​
5/18/20213:365/18/20214:33
0.95​
5/18/202119:335/18/202122:18
2.75​
5/19/202112:175/19/202114:10
1.88​
5/20/20218:045/20/202112:11
4.12​
5/25/202115:145/25/202118:04
2.83​
5/26/202111:295/26/202117:39
6.17​
5/28/20219:105/28/20219:33
0.38​
5/20/202119:205/26/202122:33
147.22​
5/26/202123:115/27/20211:16
2.08​
6/7/202119:036/7/202119:45
0.70​
6/8/202111:126/8/202113: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 a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,870
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Jun 6, 2017
Messages
7
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
Joined
Dec 3, 2018
Messages
12,870
Office Version
  1. 2007
Platform
  1. Windows
I don't understand, could you explain it with the same example?
 

jknight291

New Member
Joined
Jun 6, 2017
Messages
7

ADVERTISEMENT

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
Joined
Dec 3, 2018
Messages
12,870
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Jun 6, 2017
Messages
7
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
Joined
Dec 3, 2018
Messages
12,870
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,355
Members
418,498
Latest member
nattynat

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top