Counting the number of times a date occurs in multiple start and end date ranges.

Hi,

I have a list of start and end dates and would like to count the number of times each date throughout the year appears in these date ranges so that I can plot them to show the busiest periods.

I have attached an example file. I have been puzzling on this for a while, I was thinking it would be easy to use a COUNTIFS formula to decide whether the date in column D was equal or greater than the date in column B and then if it was equal or less than the date in column C. I have had a brain fade now...I can't remember what I've done now

Could someone please point me back in the right direction with this?

Thanks very much..

what are the date ranges you want to use

seems to be two different things here -
1) comparing the planned start and actual start and planned end and actual end
2) count the number of dates in a group

can you perhaps give a little more detail or example onthe spreadsheet of the outcome you would like

Hi etaf,

Thanks for the reply...and yes sorry I had formatted the data a little better but it seems I forgot to save it before uploading it. I'm at home now and I've re-done it to be a bit easier to understand. I want to be able to count the number of projects that are on the go at any one time so in the revised example file from the 03/04 there will be 2 projects being worked on until the 22nd when one of them will finish and then the second project finishes on the 29th. After that there is a gap until the 6 'blue' projects start on 03/04 and then the 'green' projects on the 8th which run alongside the 'blue' projects until they finish. There are some other projects further down the list that start in these times, they're not all date ordered, that's how they are on the large file I was given.

I was thinking of a formula in column F that would look down the start and end date columns and check to see if the date in column E was between or inclusive of those dates and if so to count it as 1 for each time it occurs to give a total number of projects that are being worked on on that particular date in column E. We won't have the capacity to complete all the projects 'in house' and will have to outsource some of them. I'm trying to highlight where it is we'll be over capacity so that we can plan the outsource work. The Planned and Actual columns in the previous Example file are because nothing ever seems to go perfectly to plan ...and so i'd also like to highlight that when the time comes but that is basically the same as what I'm trying to do with the second example file.

I've been trying to come up with a COUNTIFS formula that would work this out but I'm obviously doing something wrong. I was thinking along the lines of

=COUNTIFS($B$4:$B$210,">="&E4,$C$4:$C$210,"<="&E4)

...but it gives me a result of zero...I wanted to be able to copy it down alongside the dates in column E and then plot the results

Thanks for any advice you can give me...

nowhere is c column less that $E$4

if you just want to flag the result against each project as a 1 or 0 to plot would this work

=IF(AND(B4>=E4,C4<=E4),1,0)

so if start date is after or equal to the date in E - is that what you mean
and end date is less that or equal to date in E

not sure what E represents

but if you want to count the projects that have a startdate and enddate between the column E date
=IF(AND(E4>=B4,E4<=C4),"Yes", "No") or to count
=IF(AND(E4>=B4,E4<=C4),1, 0)
or if you have the number of projects in column
=IF(AND(E4>=B4,E4<=C4),F4,0)

But you have a time of 08:00 in column B and 00:00 in column E - so when the same date because of the time they do not match

so you want to compare every project start date and enddate with E4 and see if its in-between the dates and put the count in G4 of all those in-between

and then for E5 compare all start and endates to see if in-between E5 and then count in G5 all those that are between
and repeat all for all the projects

have i got that correct ?

excellent - even though not the OP - i will find that very useful - in the past i have been using text(datetimecell , "dd/mm/yyyy") and then added value =value(text(datetimecell , "dd/mm/yyyy")) - so INT will save a bit of time - thanks

I have fixed it now...feel a bit of a dumbo now though and definitely embarrassed to have to admit it on an internet forum..lol...I had the greater than and less than symbols the wrong way round....the COUNTIFS formula works now...feel a right idiot but on the other hand also happy it works. Oh well...

Thanks again for all the effort you put in trying to help, I appreciate that...

Have attached the example file again so you can see what I was trying to do...

wow..should have come back sooner...thank you all for your input

sorry, if i embarrassed you by pointing that out here --- was not my intention
i didn't notice for a while and was doing the same also
I have found years ago, when I was working on DTR (a property special program ) the syntax, commas " and ; always caused the biggest issue and usually after maybe a day of two - someone else saw it - and it was always the simplest things , that took the longest

same for VBA - but now very rusty on it - so not getting to involved with VBA yet

Thanks Dredwolf and thanks for the Sumproduct example...and etaf you haven't embarrassed me I've done that myself ....I'll blame it on the noisy office I work in....and hopefully this will teach me to check they're the correct way round next time..lol

Thanks again..now off to eat something