Formula to count number of time stamps within a range in a column having dates formatted as "custom"

[Sam]

Hi Gang,

I looked for quite some time trying to figure this out on my own but
no luck. I created an inventory spreadsheet that takes a download from
Oracle11i of all transactions posted within a 24-hour period. The
downloaded information is on one worksheet and a second worksheet in
the same file uses formulas to count various types of transactions and
calculate cost from the download. In the download there are various
columns containing quantity transacted, from and to departments,
transaction types and a reference section for users to input comments
when they transact. Usually the file has about 3000 rows. All of these
files have a date time stamp of when the transactions were processed.
Example: "6/18/2007 23:50". I believe the default format is "special"
for the date but I use a macro to change the format from military time
to regular time. (The macro also cleans up the initial download, which
has a lot of duplicate information).

Problem:
Where I work there are two shifts, one starts at 5:45 am to 5:45 pm
(days) and the other is from 5:45pm to 5:45am (nights). What I don't
know how to do is count the number of transactions that occurred
within the shift time frames. I want to avoid using a pivot table if
possible to keep down file size. Is there a formula instead that will
count the specific number of transactions that occurred within my
given time frames? I tried using a count if formula, but noticed if
the data is a "custom" or "date" format the formula will not work.
Also if possible I want to avoid creating another column to compute
the formula with. I guess I'm just looking for a formula to count the
time stamps within the criteria using the original column.

Objective:
To count how many transactions were posted on day and night shifts.

Sorry for the long post, just wanted to give as much detail as
possible.

Deepest thanks all!

Sam

 

[Guest]

try:

=SUMPRODUCT(--(MOD($A$1:$A$100,1)>=TIME(5,45,0)),--(MOD($A$1:$A$100,1)<=TIME(17,44,0)))

=SUMPRODUCT(--(MOD($A$1:$A$100,1)<TIME(5,45,0)))+SUMPRODUCT(--(MOD($A$1:$A$100,1)>TIME(17,45,0)))

should give total number of (all types of) transactions in your time
periods, assuming column A has date/time stamp.

HTH

 

[Guest]

Toppers' formula seems to work.
Sometimes it is better to create a new column. Toppers' absolute reference
wouldn't update when new data is added.
If the date/time is in column B, try entering this formula in cell A1. Drag
for entire column.

=IF(AND(MOD(B1,1)>=TIME(5,45,0),MOD(B1,1)<TIME(17,45,0)=TRUE,"Day","Night")

Then count "Day" and "Night"

=COUNTIF(A:A,"Day")
=COUNTIF(A:A,"Night")

 

[Sam]

Toppers' formula seems to work.
Sometimes it is better to create a new column. Toppers' absolute reference
wouldn't update when new data is added.
If the date/time is in column B, try entering this formula in cell A1. Drag
for entire column.

=IF(AND(MOD(B1,1)>=TIME(5,45,0),MOD(B1,1)<TIME(17,45,0)=TRUE,"Day","Night")

Then count "Day" and "Night"

=COUNTIF(A:A,"Day")
=COUNTIF(A:A,"Night")

Hi Tevuna,

In this case Toppers formula is ok because I didn't mention (although
I should have) that I get a new download daily. I keep the report
template, do a file save as, copy the formulas over one column, (new
day) convert the formulas for the previous day on the summary
worksheet to values and clear the old data on the second worksheet
(the one with the download). So everyday my download is different, as
I am not pasting new date data below the previous day. I tried Toppers
solution and it works like a champ.

My thanks to both of you for your time and help!

Sam