Extracting Date from cell with Day and Date Data

Thresh1642

New Member
Joined
Mar 14, 2017
Messages
8
Hi,

I have a series of dates that include the day and the date:
Mon, 3/4/2020
Tue, 3/12/2020
Wed, 3/20/2020
Thu, 3/28/2020

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
You can extract the date portion as a date, like this:
Code:
=DATEVALUE(MID(A1,FIND(" ",A1)+1,10))

So, if you wanted to do the substraction of A2-A1 in one step, try this:
Code:
=DATEVALUE(MID(A2,FIND(" ",A2)+1,10))-DATEVALUE(MID(A1,FIND(" ",A1)+1,10))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad to help!:)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Mon, 3/4/2020</td><td style="text-align: right;;">3/4/2020</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Tue, 3/12/2020</td><td style="text-align: right;;">3/12/2020</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Wed, 3/20/2020</td><td style="text-align: right;;">3/20/2020</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Thu, 3/28/2020</td><td style="text-align: right;;">3/28/2020</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet622</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=MID(<font color="Blue">A1,FIND(<font color="Red">",",A1</font>)+2,99</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=MID(<font color="Blue">A2,FIND(<font color="Red">",",A2</font>)+2,99</font>)-MID(<font color="Blue">A1,FIND(<font color="Red">",",A1</font>)+2,99</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,061
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top