SUM IF Duration Is Between Certain Values

Frenzyy

New Member
Joined
May 26, 2019
Messages
20
Office Version
365
Platform
Windows
Hi All! Could you please kindly advise as to how to sum duration values that fall between certain ranges? My custom format is set to [h]:mm and cannot be changed.
Let me illustrate what I'm trying to do with an example.

Columns A & B usually contain 9:00 & 17:30 respectively, however, at times they may contain values that differ. E.g. 9:00 & 20:20 or 7:50 & 22:35. Now, what I'm trying to do is sum up the hours that come before "9:00" in column A and after "22:00" in column B. Meaning that the result for the latter example would be 1:10 + 0:35 = 2:45. After that, I need to sum up all the rows containing the mentioned sums but I assume that this will be a simple SUM calculation though feel free to correct me if I'm wrong.

Please let me know if you have questions.

Many thanks!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
You could try this:

=IF(COUNT(A1:B1)=2,MAX(TIME(22,0,0),MOD(B1,1))-MIN(TIME(9,0,0),MOD(A1,1))-TIME(22,0,0)+TIME(9,0,0),0)
 

Frenzyy

New Member
Joined
May 26, 2019
Messages
20
Office Version
365
Platform
Windows
Hi, thank you for that. However, this doesn't allow for time "24:00" to be entered as it just treats it as "0".

Please see my below solution for the "after 22:00" part and I'd appreciate if you could assist me in figuring out how to properly adjust this for the "before 9:00" as I'm not able to get it to work for some reason.

=SUMPRODUCT((range-TIME(22,0,0))*(range>TIME(22,0,0)))

Thanks!
 

Frenzyy

New Member
Joined
May 26, 2019
Messages
20
Office Version
365
Platform
Windows
That's correct but please note that the formula I posted does count it as "+2" if "24" is the input. Thanks!
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
Ok do you ever have times that cross midnight?
 

Frenzyy

New Member
Joined
May 26, 2019
Messages
20
Office Version
365
Platform
Windows
I do not.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
In that case you could remove the mod part:

=IF(COUNT(A1:B1)=2,MAX(TIME(22,0,0),B1)-MIN(TIME(9,0,0),A1)-TIME(22,0,0)+TIME(9,0,0),0)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
And here is your SUMPRODUCT:

=SUMPRODUCT(((TIME(9,0,0)-A1:A10)*(A1:A10<TIME(9,0,0))*(A1:A10<>"")*(B1:B10<>""))+((B1:B10-TIME(22,0,0))*(B1:B10>TIME(22,0,0))*(A1:A10<>"")*(B1:B10<>"")))
 

Frenzyy

New Member
Joined
May 26, 2019
Messages
20
Office Version
365
Platform
Windows
That's great, thank you very much.
Would you mind explaining a little bit as to how this part works "*(A1:A10<>"")*(B1:B10<>""))"?
 

Watch MrExcel Video

Forum statistics

Threads
1,100,140
Messages
5,472,751
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top