Counting dates & Times

  • Thread starter Thread starter Willem
  • Start date Start date
W

Willem

Dear readers, I am still sitting with this problem.

I have a report showing competition entries with dates and times in seperate
fields, dates are in the 12/02/2004 format and the times are in the 13:50:23
format. The reports runs over a period of a few weeks.

I need to extract the following from it:

-Amount of entrants per week. (I made a hap hazard system for this but will
simplify it later, if you have something I can use please)
-Amount of entrants per day per week to make a weekly graph.
-Count the amount of entries per hour per day to make a graph from it so
that we can see what the trends are in entering.

I bought the book Excel formulas and it has helped me a lot but I can not
get this right. Please assist if possible.

Thank you.
 
Willem said:
Dear readers, I am still sitting with this problem.

I have a report showing competition entries with dates and times in seperate
fields, dates are in the 12/02/2004 format and the times are in the 13:50:23
format. The reports runs over a period of a few weeks.

I need to extract the following from it:

-Amount of entrants per week. (I made a hap hazard system for this but will
simplify it later, if you have something I can use please)
-Amount of entrants per day per week to make a weekly graph.
-Count the amount of entries per hour per day to make a graph from it so
that we can see what the trends are in entering.

I bought the book Excel formulas and it has helped me a lot but I can not
get this right. Please assist if possible.

Thank you.
Click to expand...

The reason why you have not got replies to this query is probably because it
is so general. You can't expect people to design a whole spreadsheet for
you! You will get replies to specific questions. If you have written a
formula and it doesn't do what you expect, post the formula and explain what
you expect as compared with what you get. If you cannot write a particular
formula, explain (specifically) what you want it to do.

To get you started, suppose you have dates in A1:A100. To count the number
of dates in the week 4th-10th Jan 2004 you could use
=SUMPRODUCT((A1:A100>=DATE(2004,1,4))*(A1:A100<=DATE(2004,1,10)))
 
Back
Top