Sum values between hour range

buer4499

New Member
Joined
Jun 15, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello,

I was wondering if there is a way I can get the total of each hour interval based on the date and time.

So example, the sum of scheduled between 00:00-01:00 but making it date dependant.

I've attached example data and how it needs to be formatted after.

Book1
ABC
1DateIntervalScheduled
205/07/202100:0028
305/07/202100:1525
405/07/202100:3019
505/07/202100:4518
605/07/202101:0019
705/07/202101:1518
805/07/202101:3024
905/07/202101:4531
1005/07/202102:0058
1105/07/202102:1587
1205/07/202102:30102
1305/07/202102:4599
1405/07/202103:00104
1505/07/202103:15107
1605/07/202103:30107
1705/07/202103:45108
1805/07/202104:00111
1905/07/202104:15102
2005/07/202104:3093
2105/07/202104:4575
2205/07/202105:0072
2305/07/202105:1560
2405/07/202105:3043
2505/07/202105:4537
2605/07/202106:0028
2705/07/202106:1523
2805/07/202106:3019
2905/07/202106:4518
3005/07/202107:0018
3105/07/202107:1516
3205/07/202107:3019
3305/07/202107:4531
3405/07/202108:0062
3505/07/202108:1597
3605/07/202108:30107
3705/07/202108:45107
3805/07/202109:00104
Sheet1



Book1
ABC
1DateIntervalScheduled
205/07/20210000
305/07/20210100
405/07/20210200
505/07/20210300
605/07/20210400
705/07/20210500
805/07/20210600
905/07/20210700
1005/07/20210800
Sheet2
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,311
Office Version
  1. 2010
Perhaps try a Pivot Table grouping time by hours
 

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
736
Could try SUMIFS

Need to change the intervals to time values though eg 00:00, 01:00, 02:00 etc

=SUMIFS(Sheet1!$C$2:$C$38,Sheet1!$A$2:$A$38,A2,Sheet1!$B$2:$B$38,"<="&B2,Sheet1!$B$2:$B$38,">"&B2-TIME(0,59,0))
 

buer4499

New Member
Joined
Jun 15, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Could try SUMIFS

Need to change the intervals to time values though eg 00:00, 01:00, 02:00 etc

=SUMIFS(Sheet1!$C$2:$C$38,Sheet1!$A$2:$A$38,A2,Sheet1!$B$2:$B$38,"<="&B2,Sheet1!$B$2:$B$38,">"&B2-TIME(0,59,0))
This has just returned a 0 value, I'll copy the actual worksheet below
 

buer4499

New Member
Joined
Jun 15, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm looking to fill in the scheduled of the below:

Leavesden CC Staffing Plan.xlsx
BCDEFGHIJKLMNO
1RussianFrenchGermanSpanishItalianSwedish
2DateIntervalScheduledIntervalScheduledIntervalScheduledIntervalScheduledIntervalScheduledIntervalScheduled
344382005/07/202100:0000000000000000000000000000
4443820.041666666666666705/07/202101:0000100001000010000100001000
5443820.083333333333333305/07/202102:0000200002000020000200002000
6443820.12505/07/202103:0000300003000030000300003000
7443820.16666666666666705/07/202104:0000400004000040000400004000
8443820.20833333333333305/07/202105:0000500005000050000500005000
9443820.2505/07/202106:0000600006000060000600006000
10443820.29166666666666705/07/202107:0000700007000070000700007000
11443820.33333333333333305/07/202108:0000800708003080070800708001
12443820.37505/07/202109:0000900709003090070900709001
13443820.41666666666666705/07/202110:0001000710003100071000710001
14443820.45833333333333305/07/202111:0001100711003110071100711001
15443820.505/07/202112:0001200712003120071200712001
16443820.54166666666666705/07/202113:0001300713003130071300713001
17443820.58333333333333305/07/202114:0001400714003140071400714001
18443820.62505/07/202115:0001500715003150071500715001
19443820.66666666666666705/07/202116:0001600716003160071600716001
20443820.70833333333333305/07/202117:0001700017000170001700017000
21443820.7505/07/202118:0001800018000180001800018000
22443820.79166666666666705/07/202119:0001900019000190001900019000
23443820.83333333333333305/07/202120:0002000020000200002000020000
24443820.87505/07/202121:0002100021000210002100021000
25443820.91666666666666705/07/202122:0002200022000220002200022000
26443820.95833333333333305/07/202123:0002300023000230002300023000
2744383106/07/202100:0000000000000000000000000000
Latest View ( Hourly)
Cell Formulas
RangeFormula
B3:B27B3=C3&D3
C3:C27C3='First View ( Hourly)'!B3
E3:E27E3=SUMIFS('Latest View (15 Mins)'!$E:$E,'Latest View ( Hourly)'!C:C,'Latest View ( Hourly)'!D3,'Latest View (15 Mins)'!$D:$D,"<="&'Latest View ( Hourly)'!D3,'Latest View (15 Mins)'!$D:$D,">"&'Latest View ( Hourly)'!D3-TIME(0,59,0))



below is my data tab

Cell Formulas
RangeFormula
B3:B36B3=C3&D3
C3C3=B1
C4:C36C4=C3
F3,F35,F31,F27,F23,F19,F15,F11,F7F3=SUM(E3:E6)
 

Forum statistics

Threads
1,143,676
Messages
5,720,242
Members
422,272
Latest member
ginkgoVil

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