count days that fall within specified quarter from large range

Hello,

I am trying to write a formula to pull in the # of days that fall within a quarter- that are in a larger range. In other words, I have contracts that span over years with different start and end dates and I would like to return how many days they fall within Q1.

Cell B2 start date 1/1/2016 Cell C2 end date 3/31/2016

Cell B4 Effective Date
Cell C4 Expiration Date

I am trying to use a countif but having trouble. I am trying to return the dates in Q1 2016 in column D

Capture.PNG

Try this in D4

=MAX(0,MIN(C4,$C$2)-MAX(B4,$B$2)+1)

OMg yes!!!! thank you so much!

Would you by chance be able to explain the formula in english so I could understand/explain it?

Originally Posted by amajor99

OMg yes!!!! thank you so much!

Would you by chance be able to explain the formula in english so I could understand/explain it?

You're welcome. Happy to help.

The formula subtracts the latest date (between B4 and B2) from the earliest date (between C4 and C2). The +1 makes sure to include the start date in the count.
The "MAX(0" section is an error handler in the case where "MIN(C4,$C$2)-MAX(B4,$B$2)+1" is negative.

If this solved your question, please mark this thread as SOLVED.

Hi- sorry I am getting an error when I did a quick gut check. in line 11 (highlighted in yellow) the managed spend days are still returning 91 days even though the contract dates are 2013-2014 and the quarter I want to return is Q1 of 2016. Do you know how to fix? Error.PNG

Can you attach your workbook so that we can take a closer look?

As you can see from my attachment, it is working fine on my end.

I'm not able to attach a file. I have the same formula in as your workbook. I have macros enabled and am using office 2013. Anything I should know?

It's something to do with my pulling the dates in from another tab- if I do =tabdata!B2 to pull the date in the formula just sets to 91

To attach a file, select Go Advanced > Manage Attachments > Choose File > select the file then "Open" > Upload > Close this window > Submit Reply.

Maybe automatic calculations are turned off? To test this, double click on cell E11 then press Enter and see if you get 0.

ok here we go

Your dates are being seen as text. Simply highlight column A > Data > Text to Columns > Finish.

Do the same for column B.