Date In and Date Out Calculation

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
Hello everyone -

I have one table with member IDs and when they joined our organization (We'll call it TableA) and another table with IDs and when they were marked "inactive" for not paying dues (TableB). My final objective is to calculate the number of years (or months...that part is flexible at this point) they have been in the organization.

In Power Query, I merged TableA with TableB using a LeftJoin (perhaps it should have been a different join?) and got the following (sorry about the formatting of the table:

ID In Out
0001
3/7/20015/14/2004
00013/7/20014/17/2006
00013/7/20014/1/2013
00016/25/20045/14/2004
00016/25/20044/17/2006
00016/25/20044/1/2013
00016/8/20065/14/2004
00016/8/20064/17/2006
00016/8/20064/1/2013
00019/16/20105/14/2004
00019/16/20104/17/2006
00019/16/20104/1/2013
000210/26/2005
000310/22/2003
000312/6/2008
00043/7/20054/1/2015
00044/13/20154/1/2015
00053/28/2002
00061/9/2003 3/30/2007
00061/9/20034/1/2008
00064/12/20073/30/2007
00064/12/20074/1/2008
000612/1/20093/30/2007
000612/1/20094/1/2008

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Question 1)
Looking at member 0006 - they came in in 2003, left in 3/2007 came back in 4/2007, left in 4/2008 and came back in 12/2009 and is still here (presumably). (there are other members more disparate than this one, so, I know there's only one month in one of the gaps). So, I need:

1/9/2003 - 3/30/2007 = 50 months
4/12/2007 - 4/1/2008 = 12 months
12/1/2009 - (today) = 83 months

for a total of approximately 145 months for 0006.

Question 2)
Now, of course things are not as nice and neat as they should be - look at 0003, they came in twice without being excluded (who knows why). In that case, I'd like to calculate 10/22/2003 - today.


I'd appreciate any suggestions on the join or an if statement.

cmcreynolds
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hello, I can speak to some of this. Number one, from the output attached, you have duplicates and I have to assume this is due to your left join. Your tables are what is creating this. Not familiar enough with Power Query, but you want to "group by" in order to remove duplicates, if allowed. Otherwise kill the duplicates to make this easier on you. Also, a left outer join/left join will always give you what is in tablex, and what matches or doesn't match in tablez. You may want an inner join so you always have an in/out. BUT, I'm making assumptions on what is in each table and what you're joining on. From there, I would think a =days360 function would get you where you need to be. Keeping in mind that days360 assumes a year is 360 days or 30 days to each month. Divide that figure by whichever you like to achieve year/month (keeping in mind the 360/year or 30/month)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top