How many are more than 15 minutes late

[Linda RQ]

Hi Everyone,

I would like to give a count of how many cells in my row have times that are
5 minutes, 10 minutes, and 15 minutes later than the set time a report is
supposed to print. I am a beginner so if it's not possible to do this
easily, I can do it by hand which is what I was planning on doing until I
started to discover the capabilities of excel.

Using Excel 2003. I was gearing up to do this by hand but perhaps it's as
easy (well, easy for you folks) as my other conditional formatting question
a few days ago. I have a spreadsheet with 2 years worth of data that shows
what time some reports actually printed. I have 730 entries for each report
time. They are supposed to print at 8, 12, 4, 8, 12, and 4

Row 1 has the times my 12am report printed
Row 2 has the times my 4am report printed
Row 3 has the times my 8am report printed
Row 4 has the times my 12pm report printed
Row 5 has the times my 4pm report printed
Row 6 has the times my 8pm report printed

I'll use row 1 as my example. I did successfully do the first part (Yay!)
which was to change the font to green for any report greater than 12:04am,
font to blue if greater than 12:09am, and font to red for anything printed
greater than 12:14am. I selected the cell then used the Format/Conditional
formatting dialog box. Now I need to count how many are green, how many are
blue, and how many are red. Is that possible?

I really didn't think it would work but I tried this anyway...I inserted the
count function in a blank cell then selected some of the green cells by hand
but my count came up with zero. It probably can't count time formats and I
imagine color is of no value in a math based program?

Thanks,
Linda

 

[Nicholas Perkins]

Hi Linda,

You are sort of on the right track with using COUNT. However I would
probably do this using a COUNTIF function, but it will require a
secondary step.

The first thing to do would be to insert a blank column between each
column of data and calculate the difference from the report time.
Firstly, I would setup some cells with the times that the reports are
meant to run. This could be in a header row or somewhere off the
sheet. This makes it easier if the report times ever change.

Then to calculate the delay will be this cell - the cell with the
printing time (probably quite similar to your conditional formatting).
This should give you a column which displays the delay time in minutes
for the printing. You could then use the COUNTIF function to count
every cell in the delay time column where the is more than 14 minutes
delay (which would be your red items).

=COUNTIF(B:B,">0:14")

You could hide the columns holding the delay in them if that data
isn't required.

There might be more elegant solutions though, but this is how I would
handle it as simply as possible.

Cheers,
Nicholas Perkins

http://www.nicholasperkins.com/blogs/

 

[Luke M]

Correct, we need to have the formula work by counting the condition, not the
colors. Here's the basic formula to follow:

=COUNTIF(1:1,">"&TIMEVALUE("12:04 am"))

You can modify this formula by adjusting the range size or the time value.
If you want to set boundaries so that you don't double count (as anything
greater than 10 minutes late is also more than 5 minutes late) you can do:

=COUNTIF(1:1,">"&TIMEVALUE("12:04 am"))-COUNTIF(1:1,">"&TIMEVALUE("12:09 am"))

You can save yourself some formula editing if you have the boundary times in
cells, then you could replace the TIMEVALUE functions with cell references.