Count - If over a certain hour of the day

[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

 

[Mike H]

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

 

[Mike H]

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

 

[T. Valko]

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

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

 

[Stacy Hill]

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

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

--
Biff
Microsoft Excel MVP











- Show quoted text -

Great!
Thanks!

 

[T. Valko]

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 -

Great!
Thanks!

 

[Stacy Hill]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP






Great!
Thanks!- Hide quoted text -

- Show quoted text -

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!

 

[barry houdini]

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

 

[Stacy Hill]

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

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

 

[barry houdini]

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

 

[Stacy Hill]

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

That works!
Great!

Thanks so much!