Excel 2007 : Subtract a date range on, around or within another date range

**tiggynook** · 05-09-2012, 09:49 AM

Hi, so I need a forumla that will calculate the number of days from one date range that are within another date range.

I have four quarters:

April 1, 2012 - June 30, 2012
July 1, 2012 - Sept 30, 2012
Oct 1, 2012 - Dec 31, 2012
Jan 1, 2013 - Mar 31, 2013

I need to know if someone took sick time (or any kind of leave) how many days they took from EACH quarter.
ie. Sick time = April 15, 2012 - July 15, 2012
=92 days in quarter 1
=15 days in quarter 2
but I would also like excel to recognize if the leave time is within the quarter range, cause if not there is no need for an equation
I will have 4 separate cells that calculate each quarters results

Note: networkdays will not work, as I need an exact count of days including weekends

**Haseeb Avarakkan** · 05-09-2012, 09:28 PM

Hi tiggynook, Welcome to the forum.

Can you please attach a sample file? So the members can try based on your data layout. If you have any confidential information, replace them with dummy.

**tiggynook** · 05-10-2012, 12:21 PM

Ok so I have attached a sample spreadhseet I am working on. I need help with the PINK section to the right, the numbers in RED are the CORRECT numbers I need to achieve with a formula.

I have entered 4 examples, I need the forumla to determine WHERE or IF the LEAVE DATES fall into the corresponding QUARTER DATES, then calculate how many DAYS were absent from EACH QUARTER.

If you need anymore information please let me know.

Thank You!!!

**Haseeb Avarakkan** · 05-10-2012, 07:49 PM

In Z5 is not be 32 instead of 62? See the attached.

**tiggynook** · 05-11-2012, 08:49 AM

Yes Z5 is supposed to be 32. Thank You

And thank you sooooo much for the formula, I just couldn't figure this one out on my own!
Thank You
Thank You
Thank You!!!

**tiggynook** · 05-11-2012, 09:14 AM

I think i spoke too soon,
When continuing to enter information into the sheet I realized that if I enter someones information who DOES NOT take any leave time it is marking the amount of days absent as the total quarter.
I need this forumal to come back with a "0" if no leave time is taken, since I am only tracking absent days in thsi column.

**Haseeb Avarakkan** · 05-11-2012, 05:14 PM

Use these formulas,

Y4,

Please Login or Register  to view this content.

Z4,

Please Login or Register  to view this content.

AA4,

Please Login or Register  to view this content.

AB4,

Please Login or Register  to view this content.

Then copy Y4:AB4 & paste down as needed.

**tiggynook** · 05-14-2012, 10:33 AM

THANK YOU!!!!

works perfectly now

**tiggynook** · 06-21-2012, 11:17 AM

So I had marked this as solved because everything was working great for a while. Now I have a new scenario and the forumla is not calculating it properly.

If one of the quarters is left blank (no dates input), and a set of leave dates is input, it is not working. I have attached an example of the error in cell AJ 172.
Quarter 1 (Peach section) was left blank becuase this particular person does not have a start date until the next quarter (blue section). The forumla I guess is referencing the blank cells to try and calculate how many days are absent from that quarter base on the leave dates (Pink section). I need the formula to come back with a "0" if any quarter has no dates in them.

I would like the forumla to recognize this for all rows, not just row 172 just in case this scenario happens again, as the information is always changing.

Let me know if this makes sense, or if you need more information.

**Haseeb Avarakkan** · 06-21-2012, 03:46 PM

Aj4: =max(0,min(j4,ai4)-max(i4,ah4)+1)*(count(ah4,ai4,i4,j4)=4)
ak4: =max(0,min(p4,ai4)-max(o4,ah4)+1)*(count(ah4,ai4,p4,o4)=4)
al4: =max(0,min(v4,ai4)-max(u4,ah4)+1)*(count(ah4,ai4,v4,u4)=4)
am4: =max(0,min(ab4,ai4)-max(aa4,ah4)+1)*(count(ah4,ai4,ab4,aa4)=4)

Excel 2007 : Subtract a date range on, around or within another date range

LinkBack

Thread Tools

Rate This Thread

Display

Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Re: Subtract a date range on, around or within another date range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions