Compare dates with timestamp and sum those ones within a range

lostxls

New Member
Joined
Jul 31, 2014
Messages
25
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello all,

I spent several hours trying to identify a solution but, I only got partial ideas which only fix partial things, not the full issue.

So I have these entries:

Start Row AEnd Row B
13/11/2021 22:41​
14/11/2021 00:45​
19/10/2021 03:33​
19/10/2021 17:02​
11/10/2021 05:23​
11/10/2021 07:55​

The 2 goals that I need to achieve are...
- Calculate the difference between dates and timestamps to see if they fit on the next 2 ranges:
"<1h" and ">1h and <2h"
- Count how many are falling in the category "<1h" and how many fall into ">1h and <2h"

Limitation, both tasks without adding a new row or column.

So as an example (adding a new column, which is not what I am looking for), calculate the time difference is easy:
StartEnd
13/11/2021 22:41​
14/11/2021 00:45​
02
19/10/2021 03:33​
19/10/2021 17:02​
13
11/10/2021 05:23​
11/10/2021 07:55​
02

I can do it with =TEXT((B1)-(A1),"hh"). As an example.

So now I have the difference between "end" and "start", in hours format, which is correct

Now I should sum those was that are falling between ">1h and <2h", that should be 2.

Ok, until here all good, the problem is: is there any way to make such calculations in one formula?
I don't want to alter the source data CSV neither add a new tab with temporal entries, I would like to reflect the values straightforward in the calculation tab.

Is there any way to do that?
I was playing around with countifs, sumifs, sumproduct... nah.
I believe it can be done buuuut I just don't see it.

Thanks in advance.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,195
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try D7

T202112a.xlsm
ABCDE
11:00 AM3:00 AM
213-11-21 22:4114-11-21 00:4502:04:00
319-10-21 03:3319-10-21 17:0213:29:00
411-10-21 05:2311-10-21 07:5502:32:00
52
62
72
6b
Cell Formulas
RangeFormula
D1D1=TIME(1,0,0)
E1E1=TIME(3,0,0)
C2:C4C2=B2-A2
D5D5=SUMPRODUCT(--(C2:C4>=D1),--(C2:C4<E1))
D6D6=SUMPRODUCT(--(B2:B4-A2:A4>=D1),--(B2:B4-A2:A4<E1))
D7D7=SUMPRODUCT(--(B2:B4-A2:A4>=0.0416666666666667),--(B2:B4-A2:A4<0.125))
 

lostxls

New Member
Joined
Jul 31, 2014
Messages
25
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello Dave,

Hitting my head with the table here, blaming myself, looks easy. I was playing around with sumproduct in the past.
Your solution is very elegant and nice but you established as a marker the 1am and 3am, while that is valid, I am only interested on these ranges:

<1h
>1h <2h
>2h <4h
>4h


I tried this (I just put the entries on another rows 17-19 here)
=SUMPRODUCT(--(B17:B19-A17:A19>="1"),--(B17:B19-A17:A19<="2")) for the option ">1h <2h"
It didn't work.
Neither I can convert date and timestamps with text ahead of the math calculation B17:B19-A17:A19 as the function doesn't support ranges.
In other words: =SUMPRODUCT(--(TEXT(B17:B19-A17:A19),"hh")>=1),--(TEXT(B17:B19-A17:A19),"hh2)<=2)) fails :/

Any alternative? I am still looking around to find out a solution.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,287
Office Version
  1. 365
Platform
  1. Windows
For 365 how about
+Fluff 1.xlsm
ABCDE
1
213/11/2021 22:4114/11/2021 00:4502<1h1
319/10/2021 03:3319/10/2021 17:0213>1h, <=2h2
411/10/2021 05:2311/10/2021 07:5502>2h, <=4h1
510/10/2021 05:2310/10/2021 06:0000>4h1
611/10/2021 05:2311/10/2021 09:5504
7
Lists
Cell Formulas
RangeFormula
E2E2=LET(Hrs,($B$2:$B$10-$A$2:$A$10)*24,SUM((Hrs>0)*(Hrs<2)))
E3E3=LET(Hrs,($B$2:$B$10-$A$2:$A$10)*24,SUM((Hrs>=2)*(Hrs<3)))
E4E4=LET(Hrs,($B$2:$B$10-$A$2:$A$10)*24,SUM((Hrs>=3)*(Hrs<5)))
E5E5=LET(Hrs,($B$2:$B$10-$A$2:$A$10)*24,SUM(--(Hrs>=5)))
C2:C6C2=B2-A2
 

lostxls

New Member
Joined
Jul 31, 2014
Messages
25
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Thanks @Fluff That is impressive, I never used "LET", so I didn't know about it.
I just adapted the solution to my XLS and I found inconsistent results, why? well. because I missed a little big detail. I have duplicated entries in my data, In other words:

1640870048147.png


The duplicates shouldn't be considered as they are altering the totals and calculations.
In other calculations, I was able to discard them with SUMPRODUCT and COUNTIF plus &"" conditions

Is there any way can extend/improve the LET formula you noted, with a condition to avoid dups under ID (column A) or empty values there?

Isunique seems not to be valid and I am afraid I might need to use "INDEX" plus "MATCH"? that would be "funny" :/
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,287
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=LET(u,UNIQUE(FILTER($B$2:$C$10,$A$2:$A$10<>"")),Hrs,(INDEX(u,,2)-INDEX(u,,1))*24,SUM((Hrs>0)*(Hrs<2)))
 
Solution

lostxls

New Member
Joined
Jul 31, 2014
Messages
25
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

@Fluff: # R E S P E C T. I don't know how many hours and fights did you have with Excel, but, I adjusted it to my Excel here and it works. I have no words, except a big "thank you"
Now I need to disassembly the formula to understand it in detail, but that falls on my side.

I also want to say thanks to @Dave Patton who gave me a hand too.

Happy new year 2022 for both, stay safe, and thanks again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,287
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,195
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you want to try the SumProduct, I provide another example with the slightly different syntax.
This version does not require the helper column; I included the helper column previously for clarity. Edit for the hour ranges that you require.
N.B. You can copy the post direct to a clean sheet. Click on the icon below the f(x) in the header below and the move to your sheet and paste.

T202112a.xlsm
ABC
1StartEnd
213-11-21 22:4114-11-21 00:45
319-10-21 03:3319-10-21 17:02
411-10-21 05:2311-10-21 07:55
511-10-21 05:2311-10-21 07:55
611-10-21 01:2311-10-21 04:55
7
8
9>=2h and <3h3
10>=3h and <4h1
11>=4h1
12
6b
Cell Formulas
RangeFormula
C9C9=SUMPRODUCT(--($B$2:$B$6-$A$2:$A$6>=2/24),--($B$2:$B$6-$A$2:$A$6<3/24))
C10C10=SUMPRODUCT(--($B$2:$B$6-$A$2:$A$6>=3/24),--($B$2:$B$6-$A$2:$A$6<4/24))
C11C11=SUMPRODUCT(--($B$2:$B$6-$A$2:$A$6>=4/24))
 

lostxls

New Member
Joined
Jul 31, 2014
Messages
25
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you want to try the SumProduct, I provide another example with the slightly different syntax.
This version does not require the helper column; I included the helper column previously for clarity. Edit for the hour ranges that you require.
N.B. You can copy the post direct to a clean sheet. Click on the icon below the f(x) in the header below and the move to your sheet and paste.

T202112a.xlsm
ABC
1StartEnd
213-11-21 22:4114-11-21 00:45
319-10-21 03:3319-10-21 17:02
411-10-21 05:2311-10-21 07:55
511-10-21 05:2311-10-21 07:55
611-10-21 01:2311-10-21 04:55
7
8
9>=2h and <3h3
10>=3h and <4h1
11>=4h1
12
6b
Cell Formulas
RangeFormula
C9C9=SUMPRODUCT(--($B$2:$B$6-$A$2:$A$6>=2/24),--($B$2:$B$6-$A$2:$A$6<3/24))
C10C10=SUMPRODUCT(--($B$2:$B$6-$A$2:$A$6>=3/24),--($B$2:$B$6-$A$2:$A$6<4/24))
C11C11=SUMPRODUCT(--($B$2:$B$6-$A$2:$A$6>=4/24))

Hi @Dave Patton that is an elegant solution however it lacks the duplicated ID check that I need, I forgot to mention that as I was using sumproduct in other calculations and I was able to remove the dups by using =SUMPRODUCT(((Details_Full_Data_data!A2:A2000<>"")/COUNTIF(Details_Full_Data_data!A2:A2000,Details_Full_Data_data!A2:A2000&"")))

I guess your approach can be fit on my setup too. I will keep it for my record. As of now the @Fluff solution works, I just needed to do some adjustments on the time ranges used but the results are accurate.

Thanks again and happy new 2022!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,241
Messages
5,836,193
Members
430,406
Latest member
pmav

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