Extract date from cell
 

If I had a cell that contains the following:
Delivered 11/24

Is there some way I can tell another cell to subtract just the date
from another date? For instance, if the other cell has 10/25 in it, I
want to use a datedif function to tell me the number of days between
the two dates (i.e. datedif(a1,a2,"d"). A1 is the cell with 10/25 in
it, and a2 is the cell with delivered 11/24 in it. I need a formula
in place of a2 that will look past the word delivered and retrieve the
date so it can complete the formula.

Any ideas?

On 3 Nov 2004 08:06:31 -0800, (Eric) wrote:

If I had a cell that contains the following:
Delivered 11/24

Is there some way I can tell another cell to subtract just the date
from another date? For instance, if the other cell has 10/25 in it, I
want to use a datedif function to tell me the number of days between
the two dates (i.e. datedif(a1,a2,"d"). A1 is the cell with 10/25 in
it, and a2 is the cell with delivered 11/24 in it. I need a formula
in place of a2 that will look past the word delivered and retrieve the
date so it can complete the formula.

Any ideas?

Assuming the format of A2 is exactly as you state, with the date at the end of
the string and following a single space, then:

=(MID(A2,1+FIND(" ",A2),255))-A1

Format the result as General or Number


--ron

Could you explain how that formula works?
=(MID(A2,1+FIND(" ",A2),255))-A1


Ron Rosenfeld wrote in message . ..
On 3 Nov 2004 08:06:31 -0800, (Eric) wrote:

If I had a cell that contains the following:
Delivered 11/24

Is there some way I can tell another cell to subtract just the date
from another date? For instance, if the other cell has 10/25 in it, I
want to use a datedif function to tell me the number of days between
the two dates (i.e. datedif(a1,a2,"d"). A1 is the cell with 10/25 in
it, and a2 is the cell with delivered 11/24 in it. I need a formula
in place of a2 that will look past the word delivered and retrieve the
date so it can complete the formula.

Any ideas?

Assuming the format of A2 is exactly as you state, with the date at the end of
the string and following a single space, then:

=(MID(A2,1+FIND(" ",A2),255))-A1

Format the result as General or Number


--ron

On 4 Nov 2004 06:28:26 -0800, (Eric) wrote:

Could you explain how that formula works?
=(MID(A2,1+FIND(" ",A2),255))-A1

For the MID argument, look in HELP. 255 is just some large number, felt to be
larger than the probable length of your text in A2.

The FIND function looks for the location of the <space, and one is added to it
so the start of the string extracted by MID will not include that space.

Once the date in A2 is extracted, the date in A1 is subtracted from that to
give the difference in days.


--ron