Thread: Calculating time to close support cases, data in two separate sheets in separate rows

Hello, I have a doozie Excel problem I can't figure out, would greatly appreciate some help.

Not sure if it matters, but we are talking Win Vista 32 bit and Excel 2007 here.

I am trying to calculate the time to resolve customer support cases based off of CRM data.

I have a dynamic export of cases in one sheet, and a dynamic export of resolutions in another, both beginning at row 24 (the rows above contain various metrics calculated off of the exported data). The number of rows containing data for the export grows daily as new support cases come in, but will always start at row 24.

What I need to compare is the created on date of the case to the created on date of the resolution and have it automatically display the time to close in the corresponding row on the case sheet. The only commonality between the case and the resolution is the title. Cases and resolutions aren't on the same row on the two worksheets -- I would imagine the case title is how they would need to be matched up.

For example:

Sheet "Cases"
Column A starting at cell A24 contains the created on dates of the cases
Column I starting at cell I24 contains the titles of the cases
Column P is where I would like to display time to close for a given case.

Sheet "Resolved Cases"
Column A starting at cell A24 contains the created on dates of the resolutions
Column D starting at cell D24 contains the titles of the cases

I have no idea where to begin on this one!! After the data is displayed in the corresponding row I can handle it but I'm at a loss to get it there.

Seems I can't post attachments, however I posted a sample file here http://www.mediafire.com/file/lh9akv...portCases.xlsx if anyone wants to check it out!!

This is the basic logic I am guessing the formual would utilize -- I just don't know how to translate into Excel formula.

If cell in column D on "resolved cases" sheet matches cell in column I on "all cases" sheet, take value of cell in same row in column A on "resolved" sheet and subtract it from value in same row in column A on "all cases" sheet and place that value in corresponding row on "all cases" sheet in column P.

Charlie, you are the best

What a relatively simple formula to cure my woes!!

Thank you so much!!

Now, I need to really read up to figure how that little text string analyzes all that data and spits out the correct answer!!