HOUR function in a COUNTIF

[rojamilo123]

Hi

I have a countif formula that works out if an item was resolved in within a target time or not. If it was, it gives a 1 and if it wasn't it gives a 0.

The formula I have works fine except for when an item was resolved on the same date, but hours after the target time. IE at the moment if it was meant to be resolved at 12pm but was resolved at 1pm, it's still giving it a pass as the date is the same. I need to adjust the formula so it takes the time and not just the date into account.

I have this currently:

=IF(COUNTIFS('$G:$G,$B16,'$L:$L,"<="&U$11,$M:$M,">"&U$11)>0,0,1)

It is the bit in bold that is the bit that checks the dates against each other. Can I add something simple in so it takes the time into account?

Thanks

[Kaper]

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

[Crooza]

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.

[rojamilo123]

Hi Kaper

The format in L and M is 'dd-mmm-yyyy hh:mm'

If this doesn't help let me know and I will put together a sample sheet.

Thanks

[Richard Buttrey]

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.

[rojamilo123]

Hi all

Sample attached.

Help is appreciated.

Cell U17 on the first sheet is the one that should return a 0 instead of 1 if you want to use that as the cell to test the formula in.

Thanks

[Kaper]

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

[rojamilo123]

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

But even if I change Row 11 to 'dd-mmm-yyyy hh:mm' format it still doesn't seem to work?

U17 still says 1.

[Kaper]

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:

Please Login or Register  to view this content.

[rojamilo123]

Thanks very much, this appears to now work!

The spelling mistake was just an error on my part!

Appreciate the help

[rojamilo123]

Hi,

On closer inspection I don't think this actually works.

It works in the sense that if the time was after the target time then it does give a 0, however it is now returning 0's for calls that were completed on the same day as the target but before the time. These should be a pass so should receive a value of 1.

Any ideas?

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:

Please Login or Register  to view this content.

[Kaper]

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