Extracting Date from cell with Day and Date Data

[Thresh1642]

Hi,

I have a series of dates that include the day and the date:[TABLE="width: 500"]
<tbody>[TR]
[TD]Mon, 3/4/2020
[/TD]
[/TR]
[TR]
[TD]Tue, 3/12/2020[/TD]
[/TR]
[TR]
[TD]Wed, 3/20/2020[/TD]
[/TR]
[TR]
[TD]Thu, 3/28/2020[/TD]
[/TR]
</tbody>[/TABLE]

I need to calculate the number of says between the dates, but to do that need to get the date out of the cell and keep it as a date.

I tried RIGHT(A1,9), and that returns 3/4/2020 with a space in front of the 3.
Doing the same to the next value returns 3/12/2020 with a space in front of the 3.
Doing A2-A1 returns the #VALUE ! error, because the forula is seeing the result of the RIGHT as text and not a date? I tried formatting the result to a date format, and either it didn't work or I am missing something, and given the number of PBKAC moments I've had today, I suspect the latter.

Thanks for the help,

Todd

 

[Joe4]

You can extract the date portion as a date, like this:

=DATEVALUE(MID(A1,FIND(" ",A1)+1,10))

So, if you wanted to do the substraction of A2-A1 in one step, try this:

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

 

[Thresh1642]

Thats what I was looking for Joe.

Appreciate the quick reply, and thanks!

Todd

 

[Joe4]

You are welcome.
Glad to help!

 

[jtakw]

Hi,

If we are doing arithmetic with the extracted "dates", we won't need to convert them:

B1 Extracts and Converts column A Dates.
C1 Extracts and Subtracts A1 Date from A2 Date:

Book1
	A	B	C
1	Mon, 3/4/2020	3/4/2020	8
2	Tue, 3/12/2020	3/12/2020	8
3	Wed, 3/20/2020	3/20/2020	8
4	Thu, 3/28/2020	3/28/2020
Sheet622

Cell Formulas
Range	Formula
B1	=MID(A1,FIND(",",A1)+2,99)+0
C1	=MID(A2,FIND(",",A2)+2,99)-MID(A1,FIND(",",A1)+2,99)