# Date Format Problem

#### kryptonian

##### Board Regular
I have a report that generates a date in this format - 12/6/2008. In excel, this is interpreted as December 6, 2008 in month/day/year format.

That's my problem. It shouldn't be interpreted as such and should be interpreted as June 12, 2008. The report comes from a client so that rules out changing the date format from the report source.

I've been tinkering with using LEFT/MID/LEN combinations but I have only managed to extract the first part of the date, which in this example is 12. The formulas I used were:

=LEFT(TEXT(A1,"m/d/yyyy"),FIND("/",TEXT(A1,"m/d/yyyy"))-1) and
=MID(TEXT(A1,"m/d/yyyy"),1,FIND("/",TEXT(A1,"m/d/yyyy"))-1)

How can I extract the text between the two "/"?

I am planning to just use concatenate afterwards to re-arrange the numbers extracted from this cell.

If someone has a better way to go around this problem, my thanks in advance!

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Jonmo1

##### MrExcel MVP
Try

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

#### Jonmo1

##### MrExcel MVP
Or actually, even better. Since 20/6/2008 is not a valid date in MM/DD/YY format, it would be interpraited as text not a date, so the Date function I suggested before won't work for that...

Instead, try Data - Text to Columns - Deliminated
On the last step, choose Date DMY

#### kryptonian

##### Board Regular
I needed a formula that is flexible enough to get the data that I want without having to determine if it is a valid date format or not, i.e., 30/6/2008 is not a valid date format since there is no month which will correspond to 30.

Anyway, my brain already went back from sleep mode and I managed to come up with this formula:

=CONCATENATE(MID(TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy"))+1,FIND("/",TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy"))+1)-FIND("/",TEXT(A6,"m/d/yyyy"))-1),"/",MID(TEXT(A6,"m/d/yyyy"),1,FIND("/",TEXT(A6,"m/d/yyyy"))-1),"/",MID(TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy"))+1)+1,LEN(TEXT(A6,"m/d/yyyy"))-FIND("/",TEXT(A6,"m/d/yyyy"),FIND("/",TEXT(A6,"m/d/yyyy")))))

The formula will get 30, 6, and 2008 from 30/6/2008 then re-arrange it to this value: 6/30/2008.

#### Jonmo1

##### MrExcel MVP
Did you try the Text To Columns?

#### jaznit

##### New Member
Did you try the Text To Columns?

Kryptonian: the Text to Columns works perfect. I have a samilar problem.

Replies
14
Views
264
Replies
7
Views
124
Replies
1
Views
205
Replies
1
Views
31
Replies
9
Views
133

1,191,501
Messages
5,986,923
Members
440,067
Latest member
Swatts1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back