HOUR function in a COUNTIF

What is the format of your data (especially in columns L and M and row 11) ?

The best would be if you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi,

The format is irrelevant, it's what's in the cells that matter.
However as has been suggested upload the workbook and manually add some typical results.

Not format in sense how the data is displayed, but what data is stored - is it just day or day with hours.

OK, let's solve this case.

The real issue is not with dates (not only with).
In B16 you have:
Planned and Cyclical maintenance (External Bulding Fabric)
while in B111 in second sheet:
Planned and Cyclical Maintenance (External Building Fabric)

can you spot the difference? i is missing in building

After correcting this we can return to main question of hours influence on date comparision..

if you want to count what happened before midnight on given day use less than givendate+1 instead of less or equal to givendate
so
=COUNTIFS('CL - FMD Calls'!$B:$B,$B16,'CL - FMD Calls'!$D:$D,"<"&'CL - FMD'!U$11+1,'CL - FMD Calls'!$E:$E,">"&'CL - FMD'!U$11)
and probably the same approach in second part (not tested it) so finally:

Formula:

=IF(COUNTIFS('CL - FMD Calls'!$B:$B,$B16,'CL - FMD Calls'!$D:$D,"<"&'CL - FMD'!U$11+1,'CL - FMD Calls'!$E:$E,">"&'CL - FMD'!U$11)>0,0,IF(COUNTIFS('CL - FMD Calls'!$B:$B,'CL - FMD'!$B16,'CL - FMD Calls'!$C:$C,"<"&'CL - FMD'!U$11+1,'CL - FMD Calls'!$A:$A,"Unresolved Non Compliant")>0,0,1))

Hi and welcome
From what I can see it looks as though you don't need to change your formula but simply the data in the columns M and U (assuming these have your dates in them). Excel stores dates and times as decimals so a twelve o clock time is simply the date (expressed as a whole number in days from 1/1/900 I think) plus 0.5 representing the 12 o'clock ie 12/24 = 0.5. 1 o'clock would be expressed as 13/24 or 0.5416666

As such I'd be amending the data in your columns to show the times as explained above.

If I've misunderstood your problem, post a copy of your worksheet.

in U11 you have full date, no hours
so you compare data in columns with 2015-12-15 00:00:00

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary. (in other words - include just few cases and show expected formula output in each case, possibly with an explanation why should be 1 or 0 in each case).