formula to count matching days between two date ranges (for large list of names)

I have two sheets. Sheet 1 and sheet 2.

Sheet1: has a list of Social Security Numbers, followed by a date
Range (i.e., start date field and end date field). The date ranges are
discrete (i.e., do not overlap) with each other for any given name.

SHEET 1
Name    A Start Date     A End Date
SSN1   1/1/09            1/5/09
SSN2   4/1/09            4/15/09
SSN1   3/2/10            3/15/10
SSN3
ETC...

Sheet 2 has the same information (i.e., multiple records with SSNs,
followed by date ranges

Name    B Start Date     B End Date   RESULT NEEDED
SSN1   1/1/09            1/31/09           5
SSN2   4/1/09            4/31/09          15
SSN1   12/2/10           12/15/10
SSN3
ETC...

Both sheets can have more than one name in multiple rows (but discrete
date ranges for that same name).

What i need to display is the count of matching days for each date
range shown (for each unique SSN) in Sheet2!. The Result Needed column
above shows a sample of what i am aiming for.  That is, i need to
compare the date range on Sheet1 to the date ranges on Sheet2 (for a
given name) to see if there is a match, and then display the count of
matching days adjacent to that date range in question in Sheet2.

Is anyone aware of a formula (or series of formulas, or macro) that
could produce that result for all records in sheet2?

Thanks for any suggestions.
0
8/20/2010 7:59:33 PM
excel 39879 articles. 2 followers. Follow

2 Replies
500 Views

Similar Articles

[PageSpeed] 51

Hello !

Hope I have understood your case.

For the example, I assumed your data in Sheet1 begin at row 2 until row 4 
(ie A2:C4)

Name your Data in Sheet1:
Name1 for A2:A4
StartDate1 for B2:B4
EndDate1 for C2:C4

I assumed your data in Sheet2 begin at row 2
and are in columns A:C

Put the following formula into cell D2 in Sheet2
=SUMPRODUCT(IF(IF(EndDate1<=C2,EndDate1,C2)-IF(StartDate1<B2,B2,StartDate1)<0,0,1+IF(EndDate1<=C2,EndDate1,C2)-IF(StartDate1<B2,B2,StartDate1)),--(Name1=A2))

This is an array formula. You must validate this formula with the 
combination of the three keys : Ctrl+Shift+Enter,
and not with the single key Enter. If the validation is correct the formula 
will be inclosed with {   }

Drag this formula down to the other rows of your data in column C.

Hope it will help you (not sure)



"Dave K" <fred.sheriff@gmail.com> a �crit dans le message de groupe de 
discussion : 
db38bddc-d6f1-41e0-b042-6679edd8a6ca@z28g2000yqh.googlegroups.com...
> I have two sheets. Sheet 1 and sheet 2.
>
> Sheet1: has a list of Social Security Numbers, followed by a date
> Range (i.e., start date field and end date field). The date ranges are
> discrete (i.e., do not overlap) with each other for any given name.
>
> SHEET 1
> Name    A Start Date     A End Date
> SSN1   1/1/09            1/5/09
> SSN2   4/1/09            4/15/09
> SSN1   3/2/10            3/15/10
> SSN3
> ETC...
>
> Sheet 2 has the same information (i.e., multiple records with SSNs,
> followed by date ranges
>
> Name    B Start Date     B End Date   RESULT NEEDED
> SSN1   1/1/09            1/31/09           5
> SSN2   4/1/09            4/31/09          15
> SSN1   12/2/10           12/15/10
> SSN3
> ETC...
>
> Both sheets can have more than one name in multiple rows (but discrete
> date ranges for that same name).
>
> What i need to display is the count of matching days for each date
> range shown (for each unique SSN) in Sheet2!. The Result Needed column
> above shows a sample of what i am aiming for.  That is, i need to
> compare the date range on Sheet1 to the date ranges on Sheet2 (for a
> given name) to see if there is a match, and then display the count of
> matching days adjacent to that date range in question in Sheet2.
>
> Is anyone aware of a formula (or series of formulas, or macro) that
> could produce that result for all records in sheet2?
>
> Thanks for any suggestions. 

0
Please9856 (13)
8/21/2010 7:56:07 PM
Notice:
There should be no empty date in your data.
Otherwise the formula will return a wrong result.



"Charabeuh" <Please@FeedBack.fr> a �crit dans le message de groupe de 
discussion : i4pb1a$o2u$1@speranza.aioe.org...
> Hello !
>
> Hope I have understood your case.
>
> For the example, I assumed your data in Sheet1 begin at row 2 until row 4 
> (ie A2:C4)
>
> Name your Data in Sheet1:
> Name1 for A2:A4
> StartDate1 for B2:B4
> EndDate1 for C2:C4
>
> I assumed your data in Sheet2 begin at row 2
> and are in columns A:C
>
> Put the following formula into cell D2 in Sheet2
> =SUMPRODUCT(IF(IF(EndDate1<=C2,EndDate1,C2)-IF(StartDate1<B2,B2,StartDate1)<0,0,1+IF(EndDate1<=C2,EndDate1,C2)-IF(StartDate1<B2,B2,StartDate1)),--(Name1=A2))
>
> This is an array formula. You must validate this formula with the 
> combination of the three keys : Ctrl+Shift+Enter,
> and not with the single key Enter. If the validation is correct the 
> formula will be inclosed with {   }
>
> Drag this formula down to the other rows of your data in column C.
>
> Hope it will help you (not sure)
 

0
Please9856 (13)
8/21/2010 11:16:05 PM
Reply:

Similar Artilces:

created new list, disabled default columns, can't modify
Hi, I created a new list using the tasks template. I've created my own columns and disabled the default ones. Just found out that doing this, I cannot modify anything. Normally, when going into a list, you can click on the default column (title I think) to modify or to get into the information that's there for modifications but because I am only using customized columns, I can't modify anything even though I have full control so it's not a permission issue. Normally what I do is to rename the default "title" list to something else I use and people are...

Can I automatically enter the current date or current time into a
Hello everyone and thanks again to the kind people who, for some reason, take the time to help me through this website. As indicated in my subject line, it seems to me that there should be an easy way to do this. Thank you- Ben Hi Ben Your subject line was truncated. I am assuming you wanted date? =today() will give you the current date. HTH "Ben" wrote: > Hello everyone and thanks again to the kind people who, for some reason, take > the time to help me through this website. > As indicated in my subject line, it seems to me that there should be an easy > way to do...

Search for values between two sheets
Hi again I think I've confused everyone with the previous thread. Is it at all possible to do something like a VLOOKUP or anythin similar to search between sheets/ workbooks I appreciate any help you can give guys Joe -- Message posted from http://www.ExcelForum.com Hi Joey I gave you one answer for two sheets. If you have several this becomes more complicated. What do you want to return? - text values - numbers For the latter one there could be a solution using SUMPRODUCT, etc. So you may explain this with a little bit more detail :-) -- Regards Frank Kabel Frankfurt, Germany ...

Viewing two folders on one screen
I know this is easy but cannot for the life of me remember how to get this. I want to be able to be able to see my tasks when I'm in my email. I want to do this without having to open two windows, I want the view to be similar to how the calendar looks, the split view....please tell me how this can be done. ...

from "date" to "text"
Hi there, I have cells formatted as date (e.g. 11/9/03) and I'd like to change the format to text. But when I do it, the values change as well (e.g. "36472"). What can I do to avoid this? Thanks You can't. Dates, to Excel, are the number of days since Jan 1, 1900. That's the what you are seeing when you try to change the format to text. You need to tell us why you want it as text. You have lots of other choices of formats available. If you absolutely have to have text, you will need to create a helper cell, and use a formula like: =text(a1,"m/d...

Date format Workbooks Open
I have an application which saves the output to .xls format. If I open the file from Excel, I get the Text Import Wizard. There is a date/time column in format dd/mm/yyyy hh:mm However, If I use Application.Workbooks.Open to open the file, the date/time column is in format dd/mm/yy hh:mm ie the yyyy is shortened to 2 digits. Manually clicking into a cell and pressing enter updates the cell, as does Text to Columns. I have recorded some code to use Data>Text to Columns, but running the code does nothing. If the dates are in column L, the code is as follows: ...

Help with a formula #3
Hello i am new her and hope you can help me with my problem: In column A i have the following value's 0, 1 and -1. Now i want in column B: we leave the 0 out, if A=-1 than column should give 1, if the next one is again -1 than B should be 2 onl after A is 1 than B should be 0 and start over again. See example below, i want to see how long a -1 streak is before an 1 i coming. A B -1 1 0 0 -1 2 0 0 1 0 0 -1 1 0 1 0 0 -1 1 0 0 -1 2 0 -1 3 0 0 1 0 Thanks, greetings Richar -- Message posted from http://www.ExcelForum.com Hi Richard one way: Enter the following in B1 as array formul...

Account List Balance and Ending balance
Hi All: When I click Account list my bank balance shows an amount say "A" dollars.. but when i go to the particular account the ending balance is not "A" dollars but "B" dollars. Why is there a discrepency?? Also the cash flow takes the "A" dollars into account while computing instead of the current ending balance "B" dollars thanks nithya ...

combinening two query results into one ?
There is a table which has [ACTIVITY_DESCRIPTION], [MATERIAL_PRICE], [LABOR_PRICE] fields. I created two queries- Query1 sorts [MATERIAL_PRICE] and Query2 sorts [LABOR_PRICE] in ascending order, according to a specific common criteria for [DESCRIPTION]. So, Query1 and Query2 produces same number of row for every query. I would like to combine query results into a single table, so i will have [MATERIAL_PRICE] and [LABOR_PRICE] in ascending order for a common criteria of [DESCRIPTION]. My target is to make a chart for values of material and labor prices in ascending order. Can I do ? hi, me...

Date formula for footer/header
I'm pretty new to excel. My question is, is there a way to have the date in the header/footer roll back one day? And if it is a Monday, can it roll back to Friday? If I use a cell for the date it throws the entire format for a loop and I end up having an entire row with just 1 thing in it. Any help would be greatly appreciated. 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code Private Sub Workbook_BeforePri...

Formula needed: count if cell CONTAINS certain text
What formula do I need to count the cells that CONTAIN a certain text (<> equal!) e.g. A1 = John A2 = Pieter A3 = William count cells that CONTAIN "i": 2 (A2 and A3) What (set of) formula should I use? Excel Help doesn't give me the answer. Wim On 7 Dec 2005 04:28:36 -0800, "Wim" <eisingspam@iafrica.com> wrote: >What formula do I need to count the cells that CONTAIN a certain text >(<> equal!) >e.g. >A1 = John >A2 = Pieter >A3 = William > >count cells that CONTAIN "i": 2 (A2 and A3) > >What (set of) formula...

Conditional Formula #2
Hi All- I want to write a formula that will look at project value and assign a % fee charge as follows: Project 0 - $ 100, Fee 10%; Project $101 -$500, Fee 8%, Project above $501, Fee 5%. I can write the formula that looks at project cost and finds fee, BUT I want to set it so that a $600 project will generate a 10% charge for first $100, 8% charge for next $399 and a 5% charge for the remaining $99. How do I do it? Many Thanks!! Assuming Project Cost is in cell A1, try this: =(MIN(A1,100)*0.1)+IF(A1>100,(A1-100)*0.08,0)+IF(A1>500,(A1-500)*0.05,0) HTH, Elkar "Captain ...

VLOOKUP formula appears in the cell I need to see the result in
I am using the VLOOKUP function but when I complete the formula, the formula just appears in the cell I need the result to show up in. Can anyone help? Hi it is possible that the cell has been formatted to text prior to the entry of the formula: click on the cell, choose format / cells - number tab and click on general and click ok. then you might need to press F2 then F9 and enter to get it to work or tools / options / view tab, untick formulas or ensure that your VLOOKUP formula has no space before the =VLOOKUP(........ -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm...

Help! Selecting data according to date range
I'm attempting to setup a worksheet for reminding employees to rene their licenses. I have input the data where the data range is from ro 3 - 84 (this could increase or decrease with hiring/firing, etc.). Th columns range from A - K with column H being *date*. I would like to start a new sheet (sheet2) in this workbook with th range of months in a year. In each monthly section it would search th data range in sheet 1 and return the records with the correspondin dates for that month. For example: If a employee's license expires i January, the entire record for that employee would...

How do I cancel an outgoing email
I tried to send an email with 2 photos attached last Friday around 10: AM. It is still in my outbox today (Monday). I can't delete it and keep getting a message that says that it's because it has already started sending. It's been more than 3 days! I've shut down my email, shut down my computer and even restored to the day before. It is still there sending. As a result, all other outgoing messages are waiting too. http://www.howto-outlook.com/faq/messagestuckinoutbox.htm should be helpful. "Connie" <Connie@discussions.microsoft.com> w...

formula required (2nd time)
dear all , i post this query second time, someone angry with me that plz do not post the query multiple but still my problem was not solve if u dont mind plz can help me anyone???? query is here under Dera plz solve my problem, i m working in a bank my query is that Today is 9-mar-10 my ending balance is 10,0000.00 in sheet 1 column A2 . and this balance automatically will insert sheet2 under (ENDING BALANCE) I want that next day on 10-MAR-10 my ending balance will that i will put in Sheet 1 coulmn (A2 )same coulm but in sheet 2 the balance of 9-mar-10 will not ch...

how to increase the speed for large datasets?
Dear all, Karl Dewey helped me to write a nice code to deal with combining records into range (below), however, it run really slow when there are over 1000 ranges. Is it possible to run every 20 or 100 ranges and combine all ranges finally to raise the speed? If yes, how to do that? Thank you very much for your help. pemt Use these two queries -- pemt_1 -- SELECT Q.Day, Q.Value, (SELECT COUNT(*) FROM [pemt] Q1 WHERE Q1.[Day] > Q.[Day] AND Q1.[Value] <> Q.[Value] )+1 AS Rank FROM pemt AS Q ORDER BY Q.Day; SELECT Min(pemt_1.Day) AS DayStart, Max(pemt...

Counting Date Changes
I'm using this formula to calculate schedule changes by workday. In this example, the difference between the original date of 8/18 and the latest move date of 9/10 is 17 workdays. What I now need to do is NOT populate Col T until I have a date in Col R, but having difficulty adding this additional criterion. Can you assist? =IF(L7="","",NETWORKDAYS(L7,IF(COUNT(M7:P7),MAX(M7:P7),Q7))) Col L Orig 08/18/05 Col M Move1 08/22/05 Col N Move2 08/30/05 Col O Move3 09/10/05 Col P Move4 Col Q Target 08/18/05 Col R A...

dates of time items on invoice
i am switching from quickbooks to money 2005 small business 1) is there a way to include the date on the time item line item? my clients need to know what date the service that is described on the invoice was provided. the date is in the file, how can i print it on the invoice? 2) is there a way to record which employee provided the service? 3) i need to email the invoice to my clients. is there a way to send the invoice with a little bit of formatting? or dies it have to be a txt file. in quickbooks (which i am switching from), i can send it as a pdf. what are my options in money? thanks...

the word count icon is missing from the review panel
Windows 2007 Word specifies a word count option on the review panel Mine is missing is it on a parameter somewhere please Thanks Kathy Franklin If your Word window is narrow enough (or your screen resolution is low enough), the ribbon starts collapsing. The first things to go are the text labels, leaving just buttons. I think this is what's happening to you -- the three rightmost buttons in the Proofing group have no labels. The one in the third row, just to the right of the Translate button, should be the Word Count button. It shows the letters ABC above a box containing...

Concatenate 2 columns date & time
Hi, I am working with Excel 2003 and have a ws that I need to combine column B which is a date, with column C which contains the time. No matter how I have formatted column D, I cannot get the date to display correctly. What I have is: B C 05/01/2010 10:55 24/12/2009 09:35 I need column D to show as: 05/01/2010 10:55 24/12/2009 09:35 I have tried several different formulas/formatting in Column D but always end u...

multiple counts on one report
Hi All, I have a report that needs to total 2 different things: Bed Days, and Clients Served. Each of these is broken down by Contract: ASOC or SACPA. I have a text box in the "Contract" Footer that counts how many clients are under each Contract. Name: Contract Count Control Source: =1 I have a text box in the Report Footer that totals the Clients Served (all clients under every Contract). Name: Clients Served Count Control Source: =Count(*) I need the same report to total: How many days each client (under each Contract) was in residence during the report period (Bed Days), and...

Using countif with a range of dates
Column Z contains multiple dates ranging over 10 years. I am trying to count the number of cells in Column A that are between two dates (e.g., 01/01/07 - 01/31/07.) I tried using =SUM((COUNTIF(Sheet2!Z:Z,"<01/31/07"))-(COUNTIF(Sheet2!Z:Z,">01/01/07"))) however, the count if not correct. The result is 3,172 via the above formula which is wrong. Using a filter, I know the correct result is 56, but I cannot correct my formula to reflect. Many Thanks! =COUNTIF(Sheet2!Z:Z,"<="&01/31/07)-COUNTIF(Sheet2!Z:Z,"<"&01/01/07) -- W...

Distribution lists #5
What's the maximum number of contacts that can be in a distribution list? ...

How to add a distribution group to the all global address list
Hello I would like to know if it's possible to add a distribution group in the all global address list. In order to avoid that user need to go in the group section to see the group list. I'm using Exch 2003 on Server 2003 and Outlook 2003 on the workstation. Help should be appreciate? all of my distribution lists appear in the GAL...yours do not? "Brice" <Brice@discussions.microsoft.com> wrote in message news:ECE99E9A-D964-42BB-BC8E-A48076B4AFA9@microsoft.com... > Hello > I would like to know if it's possible to add a distribution group in the all > gl...