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

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

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

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

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.

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

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.

Thank You!!!

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

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

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

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!!!

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

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.

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

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.

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

THANK YOU!!!!

works perfectly now

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

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.

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

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)

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1