# COUNTIF Problems



## LambChoptheKid (Dec 14, 2022)

I'm having a problem with a COUNTIFS function that I've been working with. The logic of it seems sound to me but once the date moves to 10/2/2022 things seem to break.

The idea is to see if a machine is running based on the information in the table to the right.

Can someone help me understand what might be going on?

This is the formula that is used: =IF((COUNTIFS($E:$E,$A2,$F:$F,"<=" &$B2, $G:$G, ">=" &$B2))>0, "Yes", "No")

Thank you


----------



## LambChoptheKid (Dec 14, 2022)

PS. The reason that entire columns are utilized is because the table will be continuously updated


----------



## Sufiyan97 (Dec 14, 2022)

For me it's returning Yes

Can you post you data using below link?

Book10ABCDEFGH1DateTimeRunningDate/TimeStartEnd210/1/20221:00Yes10/1/20221:008:00310/2/20222:00Yes10/1/202210:0012:00410/2/20223:00Yes10/1/202212:0013:00510/2/20225:00Yes10/1/202214:0015:00610/2/20226:00Yes10/1/202215:0016:00710/2/20227:00Yes10/2/20222:003:00810/2/20228:00Yes10/2/20225:008:0091011121314Sheet2Cell FormulasRangeFormulaC2:C8C2=IF(COUNTIFS(E:E,$A2,F:F,"<="&B2,G:G,">="&B2)>0,"Yes","No")


----------



## LambChoptheKid (Dec 17, 2022)

COUNTIF Test.xlsxABCDEFG1Date2TimeRUNNING?210/1/20221:00YesDate/TimeStartEnd310/1/20222:00Yes10/01/221:008:00410/1/20223:00Yes10/1/202210:0012:00510/1/20224:00Yes10/1/202212:0013:00610/1/20225:00Yes10/1/202214:0015:00710/1/20226:00Yes10/1/202215:0016:00810/1/20227:00Yes10/2/20222:003:00910/1/20228:00Yes10/2/20225:008:001010/1/20229:00No1110/1/202210:00Yes1210/1/202211:00Yes1310/1/202212:00Yes1410/1/202213:00Yes1510/1/202214:00Yes1610/1/202215:00Yes1710/1/202216:00Yes1810/1/202217:00No1910/1/202218:00No2010/1/202219:00No2110/1/202220:00No2210/1/202221:00No2310/1/202222:00No2410/1/202223:00No2510/1/20220:00No2610/2/20221:00No2710/2/20222:00NoShould be "Yes"2810/2/20223:00NoShould be "Yes"2910/2/20224:00No3010/2/20225:00NoShould be "Yes"3110/2/20226:00NoShould be "Yes"3210/2/20227:00NoShould be "Yes"3310/2/20228:00NoShould be "Yes"3410/2/20229:00No3510/2/202210:00No3610/2/202211:00No3710/2/202212:00No3810/2/202213:00No3910/2/202214:00No4010/2/202215:00No4110/2/202216:00No4210/2/202217:00No4310/2/202218:00No4410/2/202219:00No4510/2/202220:00No4610/2/202221:00No4710/2/202222:00No4810/2/202223:00No4910/2/20220:00NoSheet1Cell FormulasRangeFormulaC2:C49C2=IF((COUNTIFS($E:$E,$A2,$F:$F,"<=" &$B2, $G:$G, ">=" &$B2))>0, "Yes", "No")


----------



## LambChoptheKid (Dec 17, 2022)

Thanks for waiting. I had to wait until the weekend to install the necessary software


----------



## jdellasala (Dec 17, 2022)

I looked at the data in your post, and it looks like you may not understand how Excel handles Time. As you know, a DATE is stored as a whole number between 1 and a 15 digit whole number where 1 represents 1/1/1900, and today (12/17/2022) = 44912.
Similarly, Excel uses DECIMAL numbers to represent Hours, Minutes, Seconds, Fractions of a second where 0 is Midnight (12:00:00 AM) and the 24 hours of a day are broken up increments of 1/(24*60*60) or 1/86400 or roughly 0.000012 per minute. This actually works well as Noon or 12 hours is 0.5 or half of 24 hours. Other times work out nicely as well.
As such, when a Time value is greater than or equal to 1 simply doesn't count the 1 because it's part of a Date value (1/1/1900).
The Time values in your table at row 24 to the bottom are all equal to or greater than 1. In order to do an accurate determination of the end value based on hours, you'd need to use a Date and Time values and formats (*mm/dd/yyyy hh:mm AM/PM*) for both the start and end values rather than just a date and number of hours.
If you wanted to use number of hours, your Date2 value should really be a date and time, and then if you use whole numbers for hours (ASSUMING you do NOT need more than 24 hours!), the end time would be Date2+(Time/86400) where Date2 is a Date and Time value like 44835.333333 for 10/01/2022 08:00 AM.

This table demonstrates that:
Book1LMNOPQR1Date/TimeStartHoursHours ValueStart DTEnd DTHours2210/01/202201:00:00 AM08:000.33333310/01/2022 01:00 AM10/01/2022 09:00 AM08:00310/01/202210:00:00 AM12:000.50000010/01/2022 10:00 AM10/01/2022 10:00 PM12:00410/01/202212:00:00 PM13:000.54166710/01/2022 12:00 PM10/02/2022 01:00 AM13:00510/01/202202:00:00 PM15:000.62500010/01/2022 02:00 PM10/02/2022 05:00 AM15:00610/01/202203:00:00 PM16:000.66666710/01/2022 03:00 PM10/02/2022 07:00 AM16:00710/02/202202:00:00 AM03:000.12500010/02/2022 02:00 AM10/02/2022 05:00 AM03:00810/02/202205:00:00 PM08:000.33333310/02/2022 05:00 PM10/03/2022 01:00 AM08:00Sheet1Cell FormulasRangeFormulaO2:O8O2=[@Hours]P2:P8P2=L2+M2Q2:Q8Q2=[@[Start DT]]+[@Hours]R2:R8R2=[@[End DT]]-[@[Start DT]]
One other thing. Changing a value's alignment (right/center/left in particular) masks whether a cell's value is numeric (right aligned), text (left aligned) or a special value like TRUE or #N/A which are centered. It can also help in understanding what appear to be math errors, like when you KNOW a sum is wrong, and it ends up it's because it's SUMming decimal values displayed as whole numbers - or time values over 1!
Good luck.


----------



## LambChoptheKid (Dec 19, 2022)

jdellasala said:


> I looked at the data in your post, and it looks like you may not understand how Excel handles Time. As you know, a DATE is stored as a whole number between 1 and a 15 digit whole number where 1 represents 1/1/1900, and today (12/17/2022) = 44912.
> Similarly, Excel uses DECIMAL numbers to represent Hours, Minutes, Seconds, Fractions of a second where 0 is Midnight (12:00:00 AM) and the 24 hours of a day are broken up increments of 1/(24*60*60) or 1/86400 or roughly 0.000012 per minute. This actually works well as Noon or 12 hours is 0.5 or half of 24 hours. Other times work out nicely as well.
> As such, when a Time value is greater than or equal to 1 simply doesn't count the 1 because it's part of a Date value (1/1/1900).
> The Time values in your table at row 24 to the bottom are all equal to or greater than 1. In order to do an accurate determination of the end value based on hours, you'd need to use a Date and Time values and formats (*mm/dd/yyyy hh:mm AM/PM*) for both the start and end values rather than just a date and number of hours.
> ...


You are correct, I did not understand that about how excel stores time. Thank you for taking the time to explain this. I'll take some time to learn about this and reconfigure my sheet.

Thanks for the help!


----------

