Date Format Problem

kryptonian

Board Regular
Joined
Oct 6, 2006
Messages
104
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top