DATE Format Gives Weird Result

[bencar]

Hi guys,

I'm using a DATE function to format a date to MM/DD/YEAR. I want to format 31.3.14 to 3/31/2014. I used this formula:
=DATE(2000+RIGHT(C12,2),MID(C12,3,2),LEFT(C12,2))

But it results in: 12/31/2013. Why is this happening? I'm doing everything correct!

 

[philwojo]

What do you have in C12?

 

[Scott Huish]

You can just use Text to Columns to convert that.

Select your column
Click on Data, then Text to Columns
Fixed Width
Next Next
Select Date and choose DMY
Click Finish

 

[AhoyNC]

The issue is your MID(C12,3,2) is returning .3 as the month.
Scott's post above is the easy way to convert your dates.

 

[Scott Huish]

The reason you are having problems though is that your day and month aren't always 2 characters and you are starting at the wrong position. So for the 3 part, you are returning .3 to the DATE function not 3.

It would need to be MID(C12,4,1) but if you had a 1 digit month, it would be MID(C12,4,2) for a 2 digit month, same problem with the day.

 

[philwojo]

Assuming the 31.3.14 is in C12, then your formula is the issue, it is giving you a .3, not a 3 for your "mid" function. So that is why it is changing the date.

You could try this, but I'm sure there is a much better way of doing this.

=DATE(2000+RIGHT(C12,2),MID(C12,FIND(".",C12)+1,IF(MID(C12,FIND(".",C12),1)=".",1,2)),LEFT(C12,2))

 

[philwojo]

Actually, try this version, I don't think the first one works.

=DATE(2000+RIGHT(C12,2),MID(C12,FIND(".",C12)+1,IF(MID(C12,FIND(".",C12)+2,1)=".",1,2)),LEFT(C12,2))

 

[bencar]

The issue is your MID(C12,3,2) is returning .3 as the month.
Scott's post above is the easy way to convert your dates.

Ahh I see.. I didnt know periods were included as characters in the MID formula. Thanks.

You can just use Text to Columns to convert that.

Select your column
Click on Data, then Text to Columns
Fixed Width
Next Next
Select Date and choose DMY
Click Finish

That doesnt work. Couild you explain why it doesnt work>?

 

[jtakw]

Hi,

I think this is more like a follow up to this thread: https://www.mrexcel.com/forum/excel...ers-refuse-format-into-dates.html#post5046069

 

[AhoyNC]

Scott's way works for me. Make sure you choose the cell or cells that contain the date. Also be sure to select the date option when you select DMY.