Counting Dates

  • Thread starter Thread starter CPower
  • Start date Start date
C

CPower

Hi Guys,

As a part of a macro that i am doing, the amount of cases before th
week that is been worked on must be counted. Is there a way of coding
pop up to ask the user to enter the date to count back from.

i.e when compling the data for the week ending 30/06/04, all the date
before and including the 23/06/04 must be counted.


Is there any way of doing this through macro?
Any help on this would be great....

Thanks in Advance,
Cathal
 
dim dt as Date
res = InputBox("Please enter last date of count")
dt = Cdate(res)

msgbox application.countif(Range("B:B"),format(dt,"dd/mm/yy"))
 
Cathal,

I would suggest getting the user tog put the start date in a cell, and then
have a worksheet formula like

=COUNTIF(A1:A100,"<="&B1-7)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob i think this would be more suitable, but how do i get a pop u
asking the user to enter the date in cell e.g. S18, in the forma
mm/dd/yyyy, then to enter the amount of rows, with dates before e.g
05/30/2004, into cell S19???

Thanks a mil for this help.
Cathal
 
Cathal,,

I don't know where your dates are, but let's say they are in A1:R20, then
in S19, add

=COUNTIF(A1:R20,"<="&S18-7)

I was suggesting you don't bother with a pop-up, but just tell the user to
put a date in S18, the formula will then automatically count the number.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top