Find Dates On One Sheet and Calculate Days Since on Another Sheet

[wjmcmanus663]

Any help to solve this problem would be greatly appreciated, thank you in advance!
From an injury log for several departments, I would like to calculate the days since last injury for each department, on a separate sheet within the workbook.
I need a formula to pull the the latest injury date for each department and then calculate the number of days from that date.

Here is what I'm trying to accomplish:

<colgroup><col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> </colgroup><tbody>
[TD="class: xl70, width: 265, colspan: 3, align: center"] Worksheet 1
[/TD]

[TD="class: xl81"] Employee
[/TD]
[TD="class: xl82"] Date of Injury
[/TD]
[TD="class: xl69, width: 75"] Department
[/TD]

[TD="class: xl78"]John Doe[/TD]
[TD="class: xl79"]1/2/2015
[/TD]
[TD="class: xl80"]Radiology[/TD]

[TD="class: xl73"]Jane Smith[/TD]
[TD="class: xl72"]2/2/2015[/TD]
[TD="class: xl74"]Emergency[/TD]

[TD="class: xl73"]Roger Dodger[/TD]
[TD="class: xl72"]3/3/2015[/TD]
[TD="class: xl74"]MST[/TD]

[TD="class: xl73"]Lolly Pop[/TD]
[TD="class: xl72"]3/27/2015[/TD]
[TD="class: xl74"]Emergency[/TD]

[TD="class: xl73"]Charlie Glad[/TD]
[TD="class: xl72"]4/4/2015[/TD]
[TD="class: xl74"]Radiology
[/TD]

[TD="class: xl75"]Humpty Dumpty[/TD]
[TD="class: xl76"]4/15/2015[/TD]
[TD="class: xl77"]MST
[/TD]

</tbody>

____________________________________

<colgroup><col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="width:56pt" width="75"> </colgroup><tbody>
[TD="class: xl74, width: 157, colspan: 2, align: center"] Worksheet 2
[/TD]

[TD="class: xl76"] Department
[/TD]
[TD="class: xl69, width: 75"] Days Since Last Injury
[/TD]

[TD="class: xl81, width: 82"]Emergency
[/TD]
[TD="class: xl82, width: 75"]187
[/TD]

[TD="class: xl77, width: 82"]MST[/TD]
[TD="class: xl78, width: 75"]168
[/TD]

[TD="class: xl79, width: 82"]Radiology[/TD]
[TD="class: xl80, width: 75"]179
[/TD]

</tbody>

 

[DRSteele]

I think this might help you. Choose either method of column F ot G.

	A	B	C	D	E	F	G
Employee	Date of Injury	Department	Department	Days Since Last Injury	also
John Doe	Radiology	Emergency
Jane Smith	Emergency	MST
Roger Dodger	MST	Radiology
Lolly Pop	Emergency
Charlie Glad	Radiology
Humpty Dumpty	MST

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #F8CBAD"]Worksheet 1[/TD]
[TD="bgcolor: #F8CBAD, align: right"][/TD]
[TD="bgcolor: #F8CBAD, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F8CBAD"]Worksheet 2 [/TD]
[TD="bgcolor: #F8CBAD, align: right"][/TD]
[TD="bgcolor: #F8CBAD, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"]9/30/2015[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/2/2015[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]187[/TD]
[TD="bgcolor: #C6E0B4, align: right"]187[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2/2/2015[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]168[/TD]
[TD="bgcolor: #C6E0B4, align: right"]168[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3/3/2015[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]179[/TD]
[TD="bgcolor: #C6E0B4, align: right"]179[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3/27/2015[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4/4/2015[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]4/15/2015[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=DAYS($G$2,AGGREGATE(14,6,(E4=$C$4:$C$9)*($B$4:$B$9),1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G5[/TH]
[TD="align: left"]=DAYS($G$2,AGGREGATE(14,6,(E5=$C$4:$C$9)*($B$4:$B$9),1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G6[/TH]
[TD="align: left"]=DAYS($G$2,AGGREGATE(14,6,(E6=$C$4:$C$9)*($B$4:$B$9),1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]{=DAYS($G$2,MAX((E4=$C$4:$C$9)*$B$4:$B$9))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F5[/TH]
[TD="align: left"]{=DAYS($G$2,MAX((E5=$C$4:$C$9)*$B$4:$B$9))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]{=DAYS($G$2,MAX((E6=$C$4:$C$9)*$B$4:$B$9))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[wjmcmanus663]

Thank you for your assistance, I copied your worksheet formula and array formulas exactly as you wrote, but I'm getting #NAME? as my results. Any suggestions?

 

[DRSteele]

Which version of Excel are you using? Aggregate works Excel2010 onward.

And you need to invoke the array formulas with CtrlShiftEnter as the above instructions indicate.

 

[wjmcmanus663]

Sorry, still get the same result: #NAME? Can you think of anything that I might being doing wrong? I've tried both ways, but as I said before, the formulas are ver batim per your replies.

 

[DRSteele]

Which version of Excel are you using?

 

[wjmcmanus663]

I'm using Excel 2010. When I invoke the array formulas with CtrlShiftEnter I get the same results, the difference being is that the {} brackets then surround the formula.

 

[DRSteele]

Excel 2010 does not recognise the function DAYS.

So do this:

	E	F	G	H	I
Department	Days Since Last Injury	also	also	also
Emergency
MST
Radiology

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #F8CBAD"]Worksheet 2[/TD]
[TD="bgcolor: #F8CBAD, align: right"][/TD]
[TD="bgcolor: #F8CBAD, align: right"][/TD]
[TD="bgcolor: #F8CBAD, align: right"][/TD]
[TD="bgcolor: #F8CBAD, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"]9/30/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #E2EFDA, align: right"]187[/TD]
[TD="bgcolor: #C6E0B4, align: right"]187[/TD]
[TD="bgcolor: #D9E1F2, align: right"]187[/TD]
[TD="bgcolor: #B4C6E7, align: right"] 187[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA, align: right"]168[/TD]
[TD="bgcolor: #C6E0B4, align: right"]168[/TD]
[TD="bgcolor: #D9E1F2, align: right"]168[/TD]
[TD="bgcolor: #B4C6E7, align: right"] 168[/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #E2EFDA, align: right"]179[/TD]
[TD="bgcolor: #C6E0B4, align: right"]179[/TD]
[TD="bgcolor: #D9E1F2, align: right"]179[/TD]
[TD="bgcolor: #B4C6E7, align: right"] 179[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #A9D08E"]Excel 2013 onward[/TD]
[TD="bgcolor: #A9D08E, align: right"][/TD]
[TD="bgcolor: #8EA9DB"]Excel 2010 backward[/TD]
[TD="bgcolor: #8EA9DB, align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=DAYS($G$2,AGGREGATE(14,6,(E4=$C$4:$C$9)*($B$4:$B$9),1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G5[/TH]
[TD="align: left"]=DAYS($G$2,AGGREGATE(14,6,(E5=$C$4:$C$9)*($B$4:$B$9),1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G6[/TH]
[TD="align: left"]=DAYS($G$2,AGGREGATE(14,6,(E6=$C$4:$C$9)*($B$4:$B$9),1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"]=$G$2-AGGREGATE(14,6,(E4=$C$4:$C$9)*($B$4:$B$9),1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I5[/TH]
[TD="align: left"]=$G$2-AGGREGATE(14,6,(E5=$C$4:$C$9)*($B$4:$B$9),1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I6[/TH]
[TD="align: left"]=$G$2-AGGREGATE(14,6,(E6=$C$4:$C$9)*($B$4:$B$9),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]{=DAYS($G$2,MAX((E4=$C$4:$C$9)*$B$4:$B$9))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F5[/TH]
[TD="align: left"]{=DAYS($G$2,MAX((E5=$C$4:$C$9)*$B$4:$B$9))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]{=DAYS($G$2,MAX((E6=$C$4:$C$9)*$B$4:$B$9))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H4[/TH]
[TD="align: left"]{=$G$2-MAX((E4=$C$4:$C$9)*$B$4:$B$9)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H5[/TH]
[TD="align: left"]{=$G$2-MAX((E5=$C$4:$C$9)*$B$4:$B$9)}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H6[/TH]
[TD="align: left"]{=$G$2-MAX((E6=$C$4:$C$9)*$B$4:$B$9)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[wjmcmanus663]

Thank you so much, I'm now using the Aggregate formula without DAYS and it worked perfectly! I'd like to know what the 14,6 and 1 represent in the formula, if you wouldn't mind.

 

[DRSteele]

Super! I'm glad you're good to go now.

Aggregate is a function that can mimic several other functions but has the advantages of being able to circumvent the need to use CSE to do array operations and it can ignore various errors. The first argument in the function (our 14) indicates the function we want Aggregate to process (in our case, the function Large). The second argument (our 6) indicates the error handling process we want (in our case, Ignore all errors). The argument that affects Large (our 1) is used to force Large to process the k th largest value (in our case, 1, the largest value). So basically what we do there is force Aggregate to report the maximum date associated with the array formed by matching the range with the criteria.

Aggregate is very powerful. You might wish to watch this video and many others in the ExcelIsFun channel on youtube: https://www.youtube.com/watch?v=x9J2N31KTpE<strike></strike>