CODEWITHSUNDEEP
exceldatedatetimeparsing
aks85
aks85

Reputation: 805

Parse MM/DD from MM/DD/YYYY HH:MM string Excel

I'm trying to extract MM/DD as a string from a date formatted MM/DD/YY HH:MM.

I've tried using left() but the problem is that some days have 1 character and some have 2. So, in some cases, / shows up. 

Date    Date Formatted
3/12/19 12:23   3/12
3/12/19 12:47   3/12
3/13/19 5:43    3/13
3/18/19 14:43   3/18
3/19/19 7:12    3/19
3/19/19 7:50    3/19
3/19/19 7:50    3/19
3/19/19 12:17   3/19
3/19/19 20:38   3/19
3/20/19 7:37    3/20
3/21/19 14:49   3/21
3/26/19 13:05   3/26
4/2/19 8:11 4/2/
4/2/19 8:11 4/2/
4/2/19 8:11 4/2/
4/2/19 8:12 4/2/
4/2/19 8:13 4/2/
4/2/19 8:14 4/2/

I've also tried changing the date format to MM/DD but when I create a chart or pivot table, Excel still recognizes the embedded HH:MM and add unwanted data that to the chart/table.

I want MM/DD as a string. I don't want to use text --> columns, because I want a formula that auto updates as I add more data into the data set.

Thoughts?

Upvotes: 0

Views: 630

Answers (2)

Rsvraghavan
Rsvraghavan

Reputation: 11

I presume that your data in cell A1 and it is in m/dd/yy hh:mm format. To split the date alone from there i am using datevalue function along with left text function. So the datevalue function would help to convert the text date to its appropriate date value as a number according to excel stored value. The left function would help to split the appropriate text characters related to date before the blank space from the unrecognized data.

=DATEVALUE(LEFT(A1,FIND(" ",A1)-1))

Hope this is in order and it will work to help on your process.

Upvotes: 0

xQbert
xQbert

Reputation: 35353

Assuming a date formatted cell is in A1...

=MONTH(A1) &"/"&DAY(A1)

or =IF(A1<>"",MONTH(A1) &"/"&DAY(A1),"") if you want to blank dates and fill a larger range of the column.

Upvotes: 1

Related Questions