Count - If over a certain hour of the day

  • Thread starter Thread starter Stacy Hill
  • Start date Start date
S

Stacy Hill

Hi...
I am hoping someone can help me... I know there must be an easy way to
do this, but I am at a loss.
Any help would be appreciated.
I am using Excel 2003.

I am trying to add the number of times a file runs after a certain
timeframe.

For example:
Column A Column B
1 12/01/2008 1:40 PM
2 12/02/2008 4:52 PM
3 12/03/2008 4:31 PM
4 12/04/2008 3:18 PM
5 12/05/2008 11:20 AM

I want to count all files that ran after 2:00 PM. So, my total would
be 3.

Is there an easy formula to get that total?

Thanks!
Stacy
 
Hi,

Maybe this

=SUMPRODUCT((A1:A25=C1)*(A1:A25<>"")*(B1:B25>=D1))

Where
C1 contains the date you are looking for
D1 contains the time after which you want to count e.e 2:00 PM

Mike
 
I may have misread your post for after 2:00 PM on any day use

=SUMPRODUCT((A1:A25<>"")*(B1:B25>=D1))

Where like before D1 is your time


Mike
 
Another one. Assuming that 12:00 AM is the cutoff time.

=SUMPRODUCT(--(HOUR(B1:B5)>=14))
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Another one. Assuming that 12:00 AM is the cutoff time.

=SUMPRODUCT(--(HOUR(B1:B5)>=14))

--
Biff
Microsoft Excel MVP











- Show quoted text -
Click to expand...

Great!
Thanks!
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP






Great!
Thanks!- Hide quoted text -

- Show quoted text -
Click to expand...


So, to make this a little harder.... I forgot about the weekends.

My goal is to try to see if my files ran on time.
If the file ran on Saturday (or Sunday) for Friday's run, it's still
on time.
It would only be late if it ran on Monday after 2:00 PM.

For example:
Column A Column B Column C Column D

Effective Run Run Time Cut-
off Time
1 12/01/2008 12/02/2008 1:40 PM 2:00 PM
2 12/02/2008 12/02/2008 4:52 PM
3 12/03/2008 12/04/2008 4:31 PM
4 12/04/2008 12/05/2008 3:18 PM
5 12/05/2008 12/06/2008 11:20 PM

If I run the formula from above it does great on Mon-Thurs.
But for the example above, it would appear Friday is late, even though
it's not.
My total should be 2 - files ran on time and 3 - files did not.

I have tried every IF statement I can think of, and nothing works.
Any suggestions?

Again, thanks so much!
 
Hello Stacy,

Do you only have Monday to Friday dates in column A? If so try this
formula in D2 copied down

=IF(B2+C2-A2>IF(WEEKDAY(A2)=6,3,1)+"14:00"),"Late","On Time")

Now you can jsut count the "Lates".....or in a single formula to count
Lates

=SUM(IF(B2:B10+C2:C10-A2:A10>IF(WEEKDAY(A2:A10)=6,3,1)+"14:00",1))

which is an array formula that needs to be confirmed with CTRL+SHIFT
+ENTER
 
Hello Stacy,

Do you only have Monday to Friday dates in column A? If so try this
formula in D2 copied down

=IF(B2+C2-A2>IF(WEEKDAY(A2)=6,3,1)+"14:00"),"Late","On Time")

Now you can jsut count the "Lates".....or in a single formula to count
Lates

=SUM(IF(B2:B10+C2:C10-A2:A10>IF(WEEKDAY(A2:A10)=6,3,1)+"14:00",1))

which is an array formula that needs to be confirmed with CTRL+SHIFT
+ENTER
Click to expand...

I do only have Weekday dates in column A.

For some reason it does not like the first formula... it highlights
the 00" in the "14:00" and gives an error message there is an error in
your formula.
Any suggestions?

Thanks!
Stacy
 
Apologies Stacy

I have an errant parenthesis in that formula (the one immediately
after "14:00" shouldn't be there, try like this

=IF(B2+C2-A2>IF(WEEKDAY(A2)=6,3,1)+"14:00","Late","On Time")
 
Apologies Stacy

I have an errant parenthesis in that formula (the one immediately
after "14:00" shouldn't be there, try like this

=IF(B2+C2-A2>IF(WEEKDAY(A2)=6,3,1)+"14:00","Late","On Time")
Click to expand...

That works!
Great!

Thanks so much!
 
Back
Top