Count number of occurrences of a day between two dates

[AWatson]

I'm trying to analyse data relating to social care services. What I need to do is identify the number of hours of care provided in a financial year.

So for example, if Client 1 receives an hour of care per day x 7 days (we work on a Monday to Sunday week) and care starts on Wednesday 16th January 2008 and ends on Thursday 31st January 2008, I can manually work out that the client received 16 hours of care.

Another example would be Client 2 receives care Mondays for 3.5 hours, and Fridays for 1.5 hours and using the same start and end dates as the example above, again I can manually work out that the client received 10 hours as there would have been 2 Mondays x 3.5 hours and 2 Fridays x 1.5 hours between the start date of 16-01-2008 and end date of 31-01-2008.

I'm looking at around 2000 clients all with varying care needs and all with different start and end dates so what I would like to be able to do is count the number of occurences of each day between the start date and end date of care (i.e. how many Mondays, how many Tuesdays etc between date 1 and date 2) Is this possible?

Thanks

 

[dafan]

This might help:
http://www.mrexcel.com/forum/showpost.php?p=1557217&postcount=2

 

[Richard Schollar]

If you want a formula to return the number of the specific day in question then the following will do this (assume start date is in A1, end date A2):

=INT((WEEKDAY($A$1-2)-$A$1+$A2)/7)

The red 2 needs to be adjusted depending on the day in question:

1...Sunday
2...Monday
3...Tuesday
4...Wednesday
5...Thursday
6...Friday
7...Saturday

Hence, in the above formula, the number of tuesdays is returned.

 

[AWatson]

That's brilliant thanks to both of you. Although very slightly different, either version of the formula works perfectly.

 

[Richard Schollar]

And my deliberate (?!) mistake was of course that the formula returned the number of Mondays, not Tuesdays

 

[AWatson]

No problems, I had noticed (and amended accordingly) but didn't want to say!! Thanks again

 

[balaji_wizard]

How can i count number of events in July when the range has dates for the whole year?I just want to know how many records are entered for July , Sep etc.

 

[Richard Schollar]

Hi

One way would be:

=SUMPRODUCT(--(TEXT(A2:A1000,"mmm")="Jul"))

where A2:A1000 holds your event dates. Amend "Jul" to whatever month you are interested in.

Another option would be to use a Pivot Table of your data and select Group and by months.

 

[AWatson]

Re post #1 and the solution in post #3. Is it possible to work out how many Mondays, Tuesdays etc. occur on a fortnightly basis between two dates? E.g. Start date 01-Jun-09 and end date 30-Jun-09 there would be 3 Mondays, 3 Tuesdays, 2 Wednesdays etc.

Thanks again!!