Excel formula to use a countif if the number of days between 2 dates is within a range WITHOUT using a helper column to get the number of days first

[codeliftsleep]

Say I have about 3000 rows of data.

I want to compare two columns dates to each other and then countif its within a certain range---ie, 1 day, 2 days, 3 days, etc...

Except I don't want to create the "helper column" to get the # of days between the dates first, I want to do it on the fly for a few reasons....

Is this possible? Or am I forced to either use the helper column or do it in VBA?

 

[Joe4]

I don't think it is quite clear what you are after.
Are you trying to calculate the difference for each row, or one sum total of all the differences added up?

Perhaps if you post a small example with data and expected results, it will be clearer.

 

[codeliftsleep]

I don't think it is quite clear what you are after.
Are you trying to calculate the difference for each row, or one sum total of all the differences added up?

Perhaps if you post a small example with data and expected results, it will be clearer.[/QUOTE

Start Date End Date
11/17/2017 11/28/2017


I have the two columns above. I want to run the NETWORKDAYS on every cell in the column and then count them depending on how many days they are...I need various ranges like under 7 days, 7-14 days, etc
I already just added in a helper column to do it that calculates the NETWORKDAYS and then runs the countif off that column, but I didn't really want to do that. I could have done it in code, but this should be formula based.

 

[Joe4]

Start Date End Date
11/17/2017 11/28/2017

Not quite what I was looking for. Don't see any expected results there...

I need various ranges like under 7 days, 7-14 days, etc

So, you are introducing new information that wasn't mentioned in the first post, but your question is still very vague and scarce on details.
Please give us a small (like maybe 10-20 rows of data), but realistic example of your data, and show us EXACTLY what you are looking for this formula to return.

 

[codeliftsleep]

Not quite what I was looking for. Don't see any expected results there...


So, you are introducing new information that wasn't mentioned in the first post, but your question is still very vague and scarce on details.
Please give us a small (like maybe 10-20 rows of data), but realistic example of your data, and show us EXACTLY what you are looking for this formula to return.

I think I have it explained pretty well in this picture I created in Excel...or at least I hope so because I don't know how else to explain it.

https://imgur.com/a/8ZZK4

 

[Marcelo Branco]

Try something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Start Date​

[/TD]
[TD]

End date​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

11/17/2017​

[/TD]
[TD]

11/28/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

11/15/2017​

[/TD]
[TD]

11/20/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

11/20/2017​

[/TD]
[TD]

11/22/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

11/01/2017​

[/TD]
[TD]

11/14/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

11/18/2017​

[/TD]
[TD]

11/26/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

11/05/2017​

[/TD]
[TD]

11/27/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD="bgcolor: #F2DCDB"]

0-3 Days​

[/TD]
[TD="bgcolor: #F2DCDB"]

4-7 Days​

[/TD]
[TD="bgcolor: #F2DCDB"]

8-14 Days​

[/TD]
[TD="bgcolor: #F2DCDB"]

15+ Days​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD="bgcolor: #F2DCDB"]

0​

[/TD]
[TD="bgcolor: #F2DCDB"]

4​

[/TD]
[TD="bgcolor: #F2DCDB"]

8​

[/TD]
[TD="bgcolor: #F2DCDB"]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]


Put the lower value of each range (0, 4, 8, 15) in A10:D10

Formula in A11 copied across
=SUMPRODUCT(--(NETWORKDAYS(+$A$2:$A$7,+$B$2:$B$7)>=A10),--(IF(A10=15,9.99E+307,B10)>NETWORKDAYS(+$A$2:$A$7,+$B$2:$B$7)))

Hope this helps

M.