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

1. ## 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..

2. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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

3. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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

4. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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

5. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

Thanks for taking the time with the IF formulas, I appreciate that, but I need the formula to look at the whole range as there are start and end dates dotted among the columns that could include the date in column E and I need to count them all and enter the number next to each date in column E....

...and yeah the start date should be after or equal to the date in column E, and the end date should be less than or equal to the date in column E. I need it to look down the whole B and C columns to see what of those date ranges have the date in column E included. Column E is a helper column so that I can include all the dates for the year and then try and work out how many projects there are 'in progress' on those dates which I was trying to complete with a formula.

I can see that the times that are included in the dates in columns B and C are going to be a problem though, luckily I just found some VBA code to remove the times from the dates from here:

http://www.extendoffice.com/document...from-date.html

``Please Login or Register  to view this content.``
That will save me a bit of time on the master file tomorrow..

..but yeah I need to know how many projects there are that are in progress on each day of the year until the end of next January

6. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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 ?

7. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

so isnt it simply this

=COUNTIFS(\$J\$4:\$J\$210,"<="&M4,\$K\$4:\$K\$210,">="&M4)
OH I changed to get all zero time using value /text
so
J = B
K = C
M = E

all dates that are before the day E cell (my case J) so dates less than - so have started
=COUNTIFS(\$J\$4:\$J\$210,"<="&M4,\$K\$4:\$K\$210,">="&M4)
Then all dates in the list that are greater then E - so still planned to be working
would make E date inbetween

see attached

EDIT
actually you did have the correct formula ONLY you had the Signs around the other way (also taking into account the times would have been an issue
=COUNTIFS(\$B\$4:\$B\$210,">="&E4,\$C\$4:\$C\$210,"<="&E4)
so if the start date is later then the reference E cell date - so will start sometime after E4
and the END date is before the reference E cell date so will have completed before E4 date

and we want it to have started before E4 and to complete after E4

8. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

Another option :
Formula:
`Please Login or Register  to view this content.`

In G4 and drag down

This strips the times through the INT(..) function, so you dont have to edit/change the B & C columns

Hope this helps

9. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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

10. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

@ etaf, you are welcome the only problem I've ran into with it is that sumif/s and countif/s really don't work with it, that's why the sumproduct

11. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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

12. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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

13. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

Nice!, I really like the Chart

14. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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

15. ## Re: Counting the number of times a date occurs in multiple start and end date ranges.

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1