Reputation: 805
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
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
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