find date in middle of numbers and use it to calculate a cell value

**CityInspector** · 10-26-2012, 07:52 AM

I have a column that contains sample #. within the numbers is a date. Is there a way to have a macro find that date and subtract it from a different cell with a date and place the results in a new cell. I uploaded a sample worksheet to show you what I mean.

In the first row I want it find 10-25-12 and subtract it from 10-31-12 and post the answer under the turnaround column. Is this possible?

**watersev** · 10-26-2012, 08:45 AM

hi CityInspector, option

**Ron Coderre** · 10-26-2012, 08:47 AM

Using your posted workbook,
As long as the Col_B format is consistent, this regular formula subtracts the embedded date in B2 from the date in F2
I2: =F2-MID(B2,4,8)

Is that something you can work with?

**CityInspector** · 10-26-2012, 08:55 AM

I tried both options and both of the return a value of 1/6/1900.

Ron- I copied your formula exactly and pasted in I2

watersev- I copied your formula from the workbook you uploaded and pasted it in mine

**Ron Coderre** · 10-26-2012, 11:07 AM

Assuming you want the count of days...you'll need to change the number format for the formula cell from "date" to any of the appropriate number categories.

For a quick fix, try this:
• Select the formula cells
• Hold down CTRL and SHIFT while you press 1....effectively pressing CTRL+!

Does that help?