Calculate Number of Days Based on Results in another Column

pejay

New Member
Joined
Jan 24, 2017
Messages
8
Hi,

I am trying to find a way of counting the number of days between two dates dependant on the results in the last few rows of another column. In the first example below, because the result in row 6 and 7 of column B is equal, I need to calculate the number of days between these equal numbers. (21/06/2020 - 19/06/2020 = 2 days). As I add more data as in example 2, the previous calculated 2 days will be ignored, as there have been further days when the numbers in B weren't equal. At row 11 number 627 is repeated so 2 days should be retuned. Followed by 11 days and 18 days as the data is added to row 12 and 13.

I've spent a lot of time trying to solve this myself, but am struggling because of getting the formula to reference the last rows where I have data. Any help to point me in the right direction would be gratefully received.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

pejay

New Member
Joined
Jan 24, 2017
Messages
8
1592759088782.png



1592759232941.png
 

Attachments

  • 1592759150812.png
    1592759150812.png
    17.9 KB · Views: 1

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,823
Welcome to the forum!

Try:

Book1
ABC
1DateValue
201/06/2020551
305/06/2020555 
409/06/2020578 
512/06/2020581 
616/06/2020589 
719/06/2020596 
821/06/20205962
924/06/2020623 
1027/06/2020624 
1130/06/2020627 
1202/07/20206272
1311/07/202062711
1418/07/202062718
Sheet8
Cell Formulas
RangeFormula
C3:C14C3=IF(B3=B2,SUM(A3-A2,C2),"")
 

pejay

New Member
Joined
Jan 24, 2017
Messages
8
Hi Eric,

Many thanks for the formula. I think it will work just fine. I've tried it with my small sample and it gives the exact results I need. I just have to see how it will work when I try it with the formula I'm using to pick the last cell from the last row and display the result on anther sheet.

Thanks again,

Peter
 

Watch MrExcel Video

Forum statistics

Threads
1,129,921
Messages
5,639,016
Members
417,062
Latest member
CM214

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top