Counting how many days between two dates are in a quarter

[DaveCon]

Hi all,


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?

 

[Hui]

DaveCon

Assuming you have a list of quarters in the format 1/mm/yy in row 2

ie:

[pre]

D2         E2         F2         G2         H2         I2        J2          K2
1 Jan 09   1 Apr 09   1 Jul 09   1 Oct 09   1 Jan 10   1 Apr 10   1 Jul 10   1 Oct 10

[/pre]
And name your start and End Date as Named Ranges "Start" and "End"

then use something like:


D3: =IF(OR(Start>E2,End<D2),0,(E2-D2)-IF(Start>D2,Start-D2,0)-IF(End<E2,E2-End,0))


and copy across

 

[DaveCon]

Thanks Hui,


There's a few little kinks to work out (results always leave one quarter a day short while the other one is fine) but the above has given me an excellent starting point and all of the middle and a fair bit of the end so many thanks for taking the time to look at this


In case you're interested (i'm a curious kind of person) an example of the results are below


Start Date = 01/04/10, End Date = 30/09/10 = 182 Days, Q4 results 92 Days (Correct), Q1 result = 89 days (1 off)


Start Date = 01/04/10, End Date = 30/04/10 = 30 Days, Q2 results 29 days (one off)

 

[DaveCon]

Finished playing, all that was needed was to subtract 1 on the final condition/calculation of the formula so


D3: =IF(OR(Start>E2,End<D2),0,(E2-D2)-IF(Start>D2,Start-D2,0)-IF(End<E2,E2-End,0))


becomes


D3: =IF(OR(Start>E2,End<D2),0,(E2-D2)-IF(Start>D2,Start-D2,0)-IF(End<E2,E2-End-1,0))

 

[kensbro]

DaveCon- The formula below may work for you. If you are using excel 2007 it can be easily accessible.


The syntax is as follows:


=DAYS360(StartDate,EndDate,TRUE or FALSE)