|
|
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
On Dec 27, 7:44 am, Stacy Hill <N004...[ at ]gmail.com> wrote:
[Quoted Text] > 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
|
|
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
On Dec 27, 8:24 am, Mike H <m.l.hug...[ at ]ntlworld.com> wrote:
[Quoted Text] > 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
>
> On Dec 27, 7:44 am, Stacy Hill <N004...[ at ]gmail.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -
|
|
Another one. Assuming that 12:00 AM is the cutoff time.
=SUMPRODUCT(--(HOUR(B1:B5)>=14))
--
Biff
Microsoft Excel MVP
"Stacy Hill" <N004582[ at ]gmail.com> wrote in message
news:64faf636-6170-4d23-aa6d-57e708dd7fdf[ at ]f18g2000vbf.googlegroups.com...
[Quoted Text] > 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
|
|
On Dec 26, 10:30 pm, "T. Valko" <biffinp...[ at ]comcast.net> wrote:
[Quoted Text] > Another one. Assuming that 12:00 AM is the cutoff time.
>
> =SUMPRODUCT(--(HOUR(B1:B5)>=14))
>
> --
> Biff
> Microsoft Excel MVP
>
> "Stacy Hill" <N004...[ at ]gmail.com> wrote in message
>
> news:64faf636-6170-4d23-aa6d-57e708dd7fdf[ at ]f18g2000vbf.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -
Great!
Thanks!
|
|
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"Stacy Hill" <N004582[ at ]gmail.com> wrote in message
news:67483017-4c70-4b7e-a191-98f4ba9e7973[ at ]r32g2000vba.googlegroups.com...
On Dec 26, 10:30 pm, "T. Valko" <biffinp...[ at ]comcast.net> wrote:
[Quoted Text] > Another one. Assuming that 12:00 AM is the cutoff time.
>
> =SUMPRODUCT(--(HOUR(B1:B5)>=14))
>
> --
> Biff
> Microsoft Excel MVP
>
> "Stacy Hill" <N004...[ at ]gmail.com> wrote in message
>
> news:64faf636-6170-4d23-aa6d-57e708dd7fdf[ at ]f18g2000vbf.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -
Great!
Thanks!
|
|
On Dec 27, 1:08 pm, "T. Valko" <biffinp...[ at ]comcast.net> wrote:
[Quoted Text] > You're welcome. Thanks for the feedback!
>
> --
> Biff
> Microsoft Excel MVP
>
> "Stacy Hill" <N004...[ at ]gmail.com> wrote in message
>
> news:67483017-4c70-4b7e-a191-98f4ba9e7973[ at ]r32g2000vba.googlegroups.com...
> On Dec 26, 10:30 pm, "T. Valko" <biffinp...[ at ]comcast.net> wrote:
>
>
>
>
>
> > Another one. Assuming that 12:00 AM is the cutoff time.
>
> > =SUMPRODUCT(--(HOUR(B1:B5)>=14))
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > "Stacy Hill" <N004...[ at ]gmail.com> wrote in message
>
> >news:64faf636-6170-4d23-aa6d-57e708dd7fdf[ at ]f18g2000vbf.googlegroups.com....
>
> > > 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- Hide quoted text -
>
> > - Show quoted text -
>
> 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!
|
|
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
|
|
On Dec 29, 12:38 pm, barry houdini <barry.houd...[ at ]virgin.net> wrote:
[Quoted Text] > 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
|
|
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")
|
|
|