I have a spreadsheet where there are two dates (start and end) and from these I need to calculate how many days between the two dates are in each quarter. I've found a couple of issues doing this as the two dates can either span 2 quarters or be 1 month within the middle of a quarter meaning i've had to create a number of check columns to achieve this and was wondering if anyone here had any ideas on how to do this more effectively?

Essentially the column structure is as such

Start Date

End Date

Start Quarter? (If formula using median to find out which set of quarter dates the start date is between)

End Quarter? (As above but for end date)

Same Quarter? (Basic if to determine if the two dates fall within the same quarter)

Days if same quarter? (minus start from end date +1 if the same quarter)

Quarter (if same quarter which quarter is it)

Next i have two groups of 4 columns Start quarter with each quarter in a seperate column and the same for end quarter - These use an if formula saying if quarter = 0 then if start quarter = column heading then quarter end date minus start date + 1, else if quarter = column heading then use value from days if same quarter (always returns false if so) (this is essentially repeated for the end quarter group of columns with slight calculation change (end date - quarter start date +1))

This is then finally followed by another 6 columns;

Total days (sum of the last 8 columns, returns 0 where same quarter as the last 8 turn out as false values if so)

Check column (if Total days = 0 then 1 else 0)

Then 4 quarter columns (headed 1 - 4) (formula being, if check column = 0 then do nothing else check that the column heading matches quarter from the first set of calculations and if so return value from days if same quarter else put 0)

then a final set of 4 columns that sums the total days per quarter

As you can see this is ridiculously complicated and was hoping there's a more elegant way to do this?