Trying to Generate Days Between a Start Date and End Date

Lildebbie0687

I have a request that once we have 4 requests on one specific date, that those rows change color and indicate that the day is full. I can get a formula to generate based on the start or end date, but not the dates between. Example, maybe the start is 10-14 and end is 10-18. Than I have two requests with start and ends on 10-16, and than maybe a 10-16 through 10-17. As you can see there is these four requests overlap on 10/16 and that day should be considered "full". How do you indicate there are 4 tasks that are all overlapping on one day, and than flag my check box that that day is full with a date that is "in-between. I have tried AI'ing it and I cannot get anything to work for just the first part of somehow a specific date.

" the start and end?

dojones

This is a way to do it.

The two sheets in my example are 4TasksMain and 4TasksDates

Create a second sheet called 4TasksDates which contains Dates and a formula to count how many times that date occurs. Then Vlookup (or IndexMatch) to pull the count to the 4TasksMain sheet.

Here is an example.

Task Count formula:

=VLOOKUP([Start Date]@row, {4TasksDates Range 1}, 2)

Once Task Count is greater than 4, you can put in the logic you want to highlight it.

Create second sheet and pre-populate with dates. It may be easier to generate from another program like Excel and then import or paste them in.

Formula for Task Count:

=COUNTIFS({4TasksMain Start Date}, <=Date@row, {4TasksMain End Date}, >=Date@row)

Lildebbie0687

I did as you said and all I am getting is 0s on my helper sheet, and no match on my main? What am I doing wrong?

Main Log:

Paul Newcome

You don't need a second sheet or anything like that. Give this a try:

=IF(COUNTIFS([Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) >= 4, 1)