Count number of days according to a condition

With the start date in A1 and the end date in B1 this should give you a count of the days where the Closing Value is not 0. This is assuming that your have only one Opening value and one Closing value per day.

Formula:

Please Login or Register  to view this content.

This could also be calculated with COUNTIFS

Formula:

Please Login or Register  to view this content.

There are days between dates and there are dates. The list that you gave in your example has dates with gaps in between. Are you wanting to count the days covered by the date ranges or are you wanting to count the dates given? There is a huge difference. There are 5688 days between the first date and the last date but there are only 1046 dates listed.

If you enter this in P5 and select P4 and P5 then fill to the bottom of your data then sum the days with 0 the answer is 1048 days with 0. This formula is just giving the number of days for the ranges

Formula:

Please Login or Register  to view this content.

If the start of the date range is counted and the end day is counted then this is the formula to enter in P5 and filled down. This will result in 1172 days for 0.

Formula:

Please Login or Register  to view this content.

See file (2)

Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.