Counting cases between dates

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

Hi, I am using a waiting list of our clients and i would like to be able to
calculate how many are on the list, from todays date, that have been waiting
less than 6 weeks, 6 - 18 weeks and 18 weeks+. I would be very grateful for
any help with this as it's driving me mad :(
 
Hi Lisa

Suppose you have the dates in ColB try the below

'Count of clients waiting for the last 6 weeks
=COUNTIF(B:B,">" & TODAY()-(6*7))

'Count of clients waiting for the last 6 - 18 weeks
=COUNTIF(B:B,">" & TODAY()-(18*7))-COUNTIF(B:B,">" & TODAY()-(6*7))

'Count of clients waiting for 18 weeks and more
=COUNTIF(B:B,"<" & TODAY()-(18*7))
 
=COUNTIF(A1:A20,">"&TODAY()-6*7)
=COUNTIF(A1:A20,">"&TODAY()-18*7)-COUNTIF(A1:A20,">"&TODAY()-6*7)
=COUNTIF(A1:A20,"<="&TODAY()-18*7)

Adjust the range to suit.
 
Unbelieveable, i think you are a genius!!! I have no idea what any of it
meant but copied into the worksheet and it worked!!!!!!! Will it
automatically update if i wanted to do it tomorrow, next week etc??? Thank
you so much!
 
Thanks for the feedback. Yes; it works automatically as the function TODAY()
always returns the current date
 
Yes. That's why it's got TODAY() in the formula.

COUNTIF is a standard Excel function, and you'll find details of its
operation and syntax in the Excel help for the function.
 
Brilliant, thanks again for your help with this, i'd have never have got
there on my own!!!!
 
Thank you very much for your help David, excel is not my strong point!!! It
works and i'm happy :)
 
Back
Top