Excel Formula Challenge

EXCEL FORMULA CHALLENGE
I'm an Excel novice who needs a little help devising a couple of formulas. I have the need to track certain "things" on a daily basis and then summarize them and print a monthly report. These "things" can occur anywhere from 3 to 16 times daily and are time sensitive. I maintain a handwritten log until such time as I transcribe to my spreadsheet. All data entry is in 24-hour clock time. At the end of the month I do some calculations based on the daily data and print the report that also includes all the data which was entered. Here is a brief example showing some of my data and some of the calculated fields (note that I've limited the number of daily entries to make them look good on this page):

Date <=2 >=3 Tot
2/1 07:10 10:15 12:00 13:00 23:00 2 2 5
2/2 07:45 09:30 11:15 13:00 16:30 3 1 5
2/3 06:30 07:45 10:40 12:40 14:30 3 0 5
----
----
----
2/28 07:15 10:30 13:40 17:00 18:55 1 3 5

There are
5 lines of summary appear below the <=2, >=3 and Tot columns
<=2 is # of intervals which are < or = 2 hours that day
>=3 is # of intervals which are > or = 3 hours that day
Tot is the total occurrences that day

None of my Excel-savvy friends could devise formulas to calculate the results of the <=2 and >=3 daily fields. Through a great deal of trial and error I found a way to calculate these fields outside the visible spreadsheet. But can you do what my Excel-savvy friends could not?

Secondary problem: Entering all those times of day.
I found that the easiest way was to use my left hand to enter the colons and my right hand for the numbers and arrows to move from field to field. That got to be a real pain in the you-know-what so I found a better way to enter the data. Wanna know how? Reply to this post and I'll be happy to continue the thread with 2 possible solutions to the data entry problem. I can also describe the method I used to do the <=2 and >=3 calculations if you want to see that also.

If your suggestions lead me to another place to look Id be happy to give it a shot, but I'd much rather have you explain in your own words (from experience) how you would solve these little challenges.

 

Shoot!
In the brief example I had placed the column headers for <=2, >=3 and Tot over the last three columns of calculated data, but the BBS took out all the intervening spaces. Guess you should be able to figure out where the column headers go.

 

OK While Im looking at it...

What happens to the items that are between 2 and 3 hours ?


How do you work out the <=2 and >=3 ?
Do I understand you correctly if you look at the first line in the example.

The time between 7:10 and 10:15 is 3hours 5 mins,so it falls into the >=3 group. The next time to 12:00 is only 1 hour 45 mins so it falls into the <=2 group. And there are 5 times listed, hence the total of 5. Is this orrectly understood ?

and you might as well add those calculations of your into a post so we can have a look.

 

For terrafutan.
The times between <=2 and >=3 are not presented in the spreadsheet but if subtract the sum of these two figures from the total for the day the result is those time intervals between 2 and 3 hours. Your assessment of my description is exactly correct. As for my method of calculation of the <=2 and >=3 columns: I created 496 formulas BELOW the visible spreadsheet to produce the answers in each of those columns. 496 being the result of 16 occurrences times 31 days. The "time" data is entered in fields B3 thru Q33. Down on line 50 I begin the formulas for the <=2 column and down on line 100 I begin the formulas for the >=3 column. To start with I have set 3 fields as follows:
A41 0:01
A42 2:01
A43 2:59
So then, to determine if C3 minus B3 is less or equal 2 hours I use this formula in A50:
=IF(AND((C3>$A$41),(C3-B3<$A$42)),1,0)
which places a 1 in A50 if the interval between B3 and C3 is less or equal 2 else a zero there. The formulas continue across line 50 for all of "day 1." So way up there in field R3 which is the field for <=2 for day 1 of the month I use the SUM function to add up all the "1's" in row 50. A similar formula is used for all the fields to determine if interval is >=3.

Hope that answers your question.

I repeat here that if anyone wants to know how I solved the annoyance of having to enter colons in all the "time" fields feel free to ask.
71Sigma

 

Darn. I could have sworn that someone would be interested in how I resolved the data entry problem. Well, here it is anyway.
My first attempt (which ended up being successful) was to do some format manipulation. Since I usually enter a full month's data at one time I went this route:
Highlight all "data entry" cells and format them as TEXT.
Make all the entries by using a period (.) instead of a colon ) because the period is on the number pad.
Use Edit, Replace to replace all periods with colons.
Format all data cells as TIME (24-hour notation).
Voila!
But I still found it awkward to have to move between cells with the arrow keys and to type the period in every cell.

SO, I HAD A STROKE OF GENIUS (or a stroke of laziness).
I wrote a QBasic program that allows me to enter one whole day of data with absolutely no punctuation. After entering a day of data looking like this:
071508301000163522002325
the program converts it to look like this:
07:15,08:30,10:00,16:35,22:00,23:25
and writes that to a file. Continuing until the entire month exists in a .txt file and closing the file.
Then in Excel I simply import the "comma delimited" text file.
Tada!