time

[lydia]

is it possible to do an if function that would count how
many somethings happen before a certain time and how many
after? we need to know how many faxes were received
between midnight and 2:00 p.m. and how many between 2:00
p.m. and midnight, and i'm stuck! at the moment i'm
doing a count if and marking the row with either an "am"
or a "pm" but there should be a more efficient way?
thanx for your help!

 

[Akshay Bakhai]

I hope that I have understood your question correctly.

Assuming that all the times of receiving faxes are entered
as times and not as text, then the following should work.

Let us say that your times are in cells A1 through A15.

In cell A17 you want the count of faxes received before
2pm = 14h00. Thus, put in the formua as follows:

=countif(A1:A15, "<14:00")

To "parameterize" the time 2pm, let us say that you put it
in cell B1. Thus cell B1 now contains 14:00 (or 2pm if
you have formatted the cell in AM/PM). Use the following
formula in A17.

=countif(A1:A15, "<"&B1)

Hope this helps.

 

[Bob Phillips]

Lydia,

This will count all times from midnight to immediately BEFORE 2am

=SUMPRODUCT((A1:A100>=TIMEVALUE("0:00:00"))*(A1:A100<TIMEVALUE("02:00:00")))

The other part is trickier as midnight will be time 00:00:00 again, and you
can't test for that/ This

=SUMPRODUCT((A1:A100>=TIMEVALUE("02:00:00"))*(A1:A100<TIMEVALUE("23:59:59"))
)

is a compromise, but not a good one, so I would use

=COUNT(A1:A100) - B1

where B1 holds the number between midnight and 2am.

 

[Cecilkumara Fernando]

Lydia,
With midnight as one brake point it is easy
assumed you FaxInTime is in colA from A2 to A61
in B1 put 14:00
in B2 array enter (Ctrl+Shift+Enter) this formula to get the count of faxes
before 14:00 including 14:00
=SUM(IF($A$2:$A$61<=$B$1,1,))
and this for the later ones
=SUM(IF($A$2:$A$61>$B$1,1,))
above will work only if the values in colA is entered as time and if it has
the day component too (such as 03/03/03 12:34 PM) then use the following
array formula
=SUM(IF(MOD($A$2:$A$61,1)<=$B$1,1,))
=SUM(IF(MOD($A$2:$A$61,1)>$B$1,1,))
HTH
Cecil

 

[lydia]

thanx, you guys are lifesavers!

-----Original Message-----
Lydia,
With midnight as one brake point it is easy
assumed you FaxInTime is in colA from A2 to A61
in B1 put 14:00
in B2 array enter (Ctrl+Shift+Enter) this formula to get the count of faxes
before 14:00 including 14:00
=SUM(IF($A$2:$A$61<=$B$1,1,))
and this for the later ones
=SUM(IF($A$2:$A$61>$B$1,1,))
above will work only if the values in colA is entered as time and if it has
the day component too (such as 03/03/03 12:34 PM) then use the following
array formula
=SUM(IF(MOD($A$2:$A$61,1)<=$B$1,1,))
=SUM(IF(MOD($A$2:$A$61,1)>$B$1,1,))
HTH
Cecil




.

 

[lydia]

thanx!

-----Original Message-----
Lydia,

This will count all times from midnight to immediately BEFORE 2am

=SUMPRODUCT((A1:A100>=TIMEVALUE("0:00:00"))* (A1:A100<TIMEVALUE("02:00:00")))

The other part is trickier as midnight will be time 00:00:00 again, and you
can't test for that/ This

=SUMPRODUCT((A1:A100>=TIMEVALUE("02:00:00"))* (A1:A100<TIMEVALUE("23:59:59"))
)

is a compromise, but not a good one, so I would use

=COUNT(A1:A100) - B1

where B1 holds the number between midnight and 2am.

--

HTH

Bob Phillips




.

 

[lydia]

thanx!

-----Original Message-----
I hope that I have understood your question correctly.

Assuming that all the times of receiving faxes are entered
as times and not as text, then the following should work.

Let us say that your times are in cells A1 through A15.

In cell A17 you want the count of faxes received before
2pm = 14h00. Thus, put in the formua as follows:

=countif(A1:A15, "<14:00")

To "parameterize" the time 2pm, let us say that you put it
in cell B1. Thus cell B1 now contains 14:00 (or 2pm if
you have formatted the cell in AM/PM). Use the following
formula in A17.

=countif(A1:A15, "<"&B1)

Hope this helps.


.