Showing the Date instead of Sum or a workaround to solve contract - back date analysis

I'm looking to do the following.

I'm looking to count the days between a contract out and a contract back. The first instance of a 1 is the contract out and the second instance is the contract back. A 2 represents a contract coming back in the same day.

Is there any easy way to bring down the date rows to only the columns filled in so I can do a simple date - date formula to find out how long it took to get the contract back?

Is there any way during the Pivot to input the date instead of the sum?

ID 7/1/2014 7/2/2014 7/3/2014 7/7/2014 7/8/2014 7/9/2014 7/10/2014 7/11/2014 7/14/2014 7/15/2014 7/16/2014 7/17/2014
12345
12346 1 1
12366 2
12788
12333 1
12354
13544
12354
12348 1 1
12349
12348 2

That didn't paste well but essentially the 1, 2 is spread between different dates based on when the contract was sent and returned.

None of the IDs have a 1 and a 2 in their row. For example, 12346 has two 1's, 12333 has only one 1, and 12348 has only one 2. Not sure how to determine the date out and date returned as you've presented it.

Hi,

Sorry that was a bad copy paste on my part. All ID have (2) 1 for the date contract was sent out and the date contract was returned. (or a 2 if returned same day). I'm only looking at data for returned contracts.

You're right this is probably easier/better.

data-sample-out-2-back.xlsx

It'd certainly be easier if you used "Out" and "Back" instead of using the same number twice. Any way to update the data accordingly?

Originally Posted by hoyasaxa215

It'd certainly be easier if you used "Out" and "Back" instead of using the same number twice. Any way to update the data accordingly?

I can pivot the data differently but I can't think of a way to insert that. Is there a way to insert the date instead of the sum in the pivot table?

Problem solved. What I did was during the pivot I changed the date column to be the value as "Sum" then pasted that into a blank workbook and changed the formatting to be date.

Can you upload the updated file?