# Compare dates with timestamp and sum those ones within a range

#### lostxls

##### New Member
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 A End 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:
 Start End 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.

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

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:

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
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)))``

#### lostxls

##### New Member

@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
Glad we could help & thanks for the feedback.

#### Dave Patton

##### Well-known Member
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
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!

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.

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?

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