• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Subtracting dates within subgroups

P

periphery

New Member
Hello! I’ve been puzzling over how to subtract dates within a group of records when the groups are unequal sizes.


I have data that is grouped by person, identified by an ID number. That is, each person has multiple records, or lines in the table, and their ID number is the same in each one. The number of lines that each person has varies. Each line represents a service event, and within each line is the date of service. I would like to calculate how long each person has been in service by subtracting the latest service date from the earliest service date.


I know how to do pieces of this problem, but not together. If I were summing or counting the data, I could simply use a pivot table, but I don’t see how to do date subtraction in a pivot table. And I know how to do date subtraction and the max and min function, but not when the data is grouped like this. (I was searching for some sort of group or by function in Excel but could only find information on subtotaling.)


Any hints or assistance would be appreciated. Thank you!
 
Assuming you have a list of the names already, your array formula would be something like:

=MAX(IF(NameRange=Name,DateRange))-MIN(IF(NameRange=Name,DateRange))


Confirm formula using Ctlr+Shift+Enter.
 
Check out the explanation here:

http://chandoo.org/forums/topic/plain-language-definition-of-array-formulas-vs-other-formulas


Hope that helps! =)
 
Hi Luke,

That made sense, but I guess I'm confused by your reference to having a list of the names. How am I to use that?
 
Assuming this is your data in A1:A10

[pre]
Code: 
ID	NAME	Date
1	Bob	1-Jan-12
2	Bob	1-Feb-12
3	Bob	1-Mar-12
4	Tom	1-Apr-12
5	Tom	1-May-12
6	Sue	1-Jun-12
7	Sue	1-Jul-12
8	Sue	1-Aug-12
9	Sue	1-Sep-12
[/pre]
NameRange would B2:B10, DateRange would be C2:C10, and Name would be whatever specific name you are looking for.
 
But how do I tell the formula to move on to the next name? I want to do this calculation for each name in the worksheet. There are hundreds. Thanks.
 
Oh wait, that's where my list comes in, right? I can just put my list of unique names in one column, put the formula in the column beside, and make it a relative reference and copy down the list.
 
Bingo! With cell reference, formula might be something like this if the first name is in G2

=MAX(IF($B$2:$B$10=G2,$C$2:$C$10))-MIN(IF($B$2:$B$10=G2,$C$2:$C$10))
 
Thanks, Luke! It works beautifully...when my dates are formatted correctly. I'm having difficulty with that, as it works when I format the dates manually but not using formulas. Should I start a new thread for that? Thank you.
 
Actually, I figured it out. Or rather, someone else on the Internet figured it out. I'm posting a link to the solution in case anyone else has the same problem. It involves changing the format of the dates.


http://www.mrexcel.com/forum/excel-questions/357010-time-format-cell-does-not-update-until-i-double-click-cell.html
 
You must log in or register to reply here.
Back
Top