Counting cases between dates
 

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 :(
--
Many thanks, Lisa

Counting cases between dates
 

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))


--
Jacob


"Lisa" wrote:

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 :(
--
Many thanks, Lisa

Counting cases between dates
 

=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.
--
David Biddulph

"Lisa" wrote in message
...
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 :(
--
Many thanks, Lisa

Counting cases between dates
 

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!
--
Many thanks, Lisa


"Jacob Skaria" wrote:

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))


--
Jacob


"Lisa" wrote:

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 :(
--
Many thanks, Lisa

Counting cases between dates
 

Thanks for the feedback. Yes; it works automatically as the function TODAY()
always returns the current date

--
Jacob


"Lisa" wrote:

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!
--
Many thanks, Lisa


"Jacob Skaria" wrote:

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))


--
Jacob


"Lisa" wrote:

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 :(
--
Many thanks, Lisa

Counting cases between dates
 

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.
--
David Biddulph

"Lisa" wrote in message
...
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!
--
Many thanks, Lisa


"Jacob Skaria" wrote:

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))


--
Jacob


"Lisa" wrote:

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 :(
--
Many thanks, Lisa

Counting cases between dates
 

Brilliant, thanks again for your help with this, i'd have never have got
there on my own!!!!
--
Many thanks, Lisa


"Jacob Skaria" wrote:

Thanks for the feedback. Yes; it works automatically as the function TODAY()
always returns the current date

--
Jacob


"Lisa" wrote:

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!
--
Many thanks, Lisa


"Jacob Skaria" wrote:

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))


--
Jacob


"Lisa" wrote:

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 :(
--
Many thanks, Lisa

Counting cases between dates
 

Thank you very much for your help David, excel is not my strong point!!! It
works and i'm happy :)
--
Many thanks, Lisa


"David Biddulph" wrote:

=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.
--
David Biddulph

"Lisa" wrote in message
...
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 :(
--
Many thanks, Lisa


.

Counting cases between dates
 

Glad it worked for you.
--
David Biddulph

"Lisa" wrote in message
...
Thank you very much for your help David, excel is not my strong point!!!
It
works and i'm happy :)
--
Many thanks, Lisa


"David Biddulph" wrote:

=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.
--
David Biddulph

"Lisa" wrote in message
...
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 :(
--
Many thanks, Lisa


.