Calculating time to close support cases, data in two separate sheets in separate rows, share title

bgallagher1

New Member
Joined
Aug 10, 2010
Messages
5
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.

I have a sample spreadsheet -- not sure how to attach though.

EDIT: Seems I can't post attachments, however I posted the file here http://www.mediafire.com/file/lh9akvlddoiqiih/ExampleSupportCases.xlsx if anyone wants to check it out!!
 
Last edited:

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

bgallagher1

New Member
Joined
Aug 10, 2010
Messages
5
This is the basic logic I am guessing the formual would utilize -- I just don't know how to translate into Excel formula.

<TABLE style="WIDTH: 398pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=531><COLGROUP><COL style="WIDTH: 398pt; mso-width-source: userset; mso-width-alt: 19419" width=531><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 398pt; HEIGHT: 38.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=51 width=531>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.</TD></TR></TBODY></TABLE>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,369
Messages
5,595,760
Members
414,017
Latest member
surajks

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
Top