# Countifs -> Dates in 2 cells are equal & before time



## nTn (Jan 3, 2023)

Hi everyone,

I'm trying to count the amount of times 2 different cells have the same date and also the times in a 3rd cell need to match and the 4th cell need to be before/after a certain time.
In my formula I've only managed to put in the times, so if the date is incorrect it will be counted anyway. Do you know a fix or maybe a better way of doing it? Any input very appreciated.

For example:







Cell D2:     =COUNTIFS(B9:B503;"="&TIME(7;0;0);D9:D503;"<="&TIME(7;0;0))
Cell D3:     =COUNTIFS(B9:B503;"="&TIME(7;0;0);D9:D503;">="&TIME(7;0;1);D9:D503;"<="&TIME(8;0;0))
Cell D4:     =D2+D3
Cell D5:     =COUNTIFS(B9:B503;"="&TIME(7;0;0);D9:D503;">="&TIME(8;0;1))


----------



## Skybluekid (Jan 3, 2023)

Try this:
Book1BCDE1DueArrived202/01/2023 07:0002/01/2023 07:00303/01/2023 07:0004/01/2023 07:00404/01/2023 07:0004/01/2023 07:15505/01/2023 10:0005/01/2023 11:00606/01/2023 07:0006/01/2023 08:1578Due 7:00 and arrived before 7:0019Due 7:00 and arrived between 07:00:01 and before 08:00110Due 7:00 and arrived before 08:00211Due 7:00 and after 08:00.01212Arrived the wrong day1Sheet1Cell FormulasRangeFormulaE8E8=SUMPRODUCT(--(INT($B$2:$B$6)=INT($C$2:$C$6)*(MOD($C$2:$C$6,1)<=0.29167)))E9E9=SUMPRODUCT(--(INT($B$2:$B$6)=INT($C$2:$C$6)*(MOD($C$2:$C$6,1)>=0.291678)*(MOD($C$2:$C$6,1)<=0.333333)))E10E10=SUMPRODUCT(--(INT($B$2:$B$6)=INT($C$2:$C$6)*(MOD($C$2:$C$6,1)<=0.333333)))E11E11=SUMPRODUCT(--(INT($B$2:$B$6)=INT($C$2:$C$6)*(MOD($C$2:$C$6,1)>=0.33)))E12E12=SUMPRODUCT(--(INT($B$2:$B$6)<>INT($C$2:$C$6)))


----------



## ExceLoki (Jan 3, 2023)

here's another option. i also created a suggested set, but this is if you are looking for how far over due time or wanting to compare specific times.
------------------
Book1ABCDEFGH1previousexpectednewsuggestedsuggested description2due 7 arrive before 72100arrive early same day3due 7 arrive 7-81121arrive on time same day4due 7 arrive before 83222arrive within hour on time same day5due 7 arrive after 81111arrive over hour late same day6arrive wrong day111arrive wrong day78date duetime duedate arrivedtime arriveddate difftime diff92/1/20237:002/1/20237:00:0000.00103/1/20237:004/1/20237:00:00-310.00114/1/20237:004/1/20237:15:000-15.00125/1/202310:005/1/202311:00:000-60.00136/1/20237:006/1/20238:15:000-75.00Sheet1Cell FormulasRangeFormulaF2F2=COUNTIFS(E9:E503,"=0",B9:B503,"="&TIME(7,0,0),D9:D503,"<"&TIME(7,0,0))G2G2=COUNTIFS(E9:E503,"=0",F9:F503,">0")F3F3=COUNTIFS(E9:E503,"=0",B9:B503,"="&TIME(7,0,0),D9:D503,">="&TIME(7,0,0),D9:D503,"<"&TIME(8,0,0))G3G3=COUNTIFS(E9:E503,"=0",F9:F503,"=0")F4,D4F4=F2+F3G4G4=COUNTIFS(E9:E503,"=0",F9:F503,"<0",F9:F503,">=-60")F5F5=COUNTIFS(E9:E503,"=0",B9:B503,"="&TIME(7,0,0),D9:D503,">="&TIME(8,0,1))G5G5=COUNTIFS(E9:E503,"=0",F9:F503,"<-60")F6F6=COUNTIFS(A9:A503,"<>",E9:E503,"<>0")G6G6=COUNTIFS(A9:A503,"<>",E9:E503,"<>0")D2D2=COUNTIFS(B9:B503,"="&TIME(7,0,0),D9:D503,"<="&TIME(7,0,0))D3D3=COUNTIFS(B9:B503,"="&TIME(7,0,0),D9:D503,">="&TIME(7,0,1),D9:D503,"<="&TIME(8,0,0))D5D5=COUNTIFS(B9:B503,"="&TIME(7,0,0),D9:D503,">="&TIME(8,0,1))E9:E13E9=A9-C9F9:F10,F12:F13F9=(B9-D9)*1440F11F11=IF(((B11-D11)*1440)<0,(B11-D11)*1440,(B11-D11)*1440)


----------



## nTn (Jan 4, 2023)

Thanks to the both of you for taking your time to help me.
@ExceLoki your solution seemed to fit better for my scenario and I've now applied it with a few adjustments. Works great and I can track the data correctly!


----------



## ExceLoki (Jan 4, 2023)

glad to help. thanks for the feedback


----------

