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

Board Regular
Joined
Apr 14, 2017
Messages
103
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?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
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

Board Regular
Joined
Apr 14, 2017
Messages
103
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
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

Board Regular
Joined
Apr 14, 2017
Messages
103
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
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Try something like this


A
B
C
D
1
Start Date​
End date​
2
11/17/2017​
11/28/2017​
3
11/15/2017​
11/20/2017​
4
11/20/2017​
11/22/2017​
5
11/01/2017​
11/14/2017​
6
11/18/2017​
11/26/2017​
7
11/05/2017​
11/27/2017​
8
9
0-3 Days​
4-7 Days​
8-14 Days​
15+ Days​
10
0​
4​
8​
15​
11
1​
2​
2​
1​

<tbody>
</tbody>


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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,369
Messages
5,444,059
Members
405,264
Latest member
JohnP1972

This Week's Hot Topics

Top