I have an Excel column that uses US date format, i.e. MM/DD/YYYY, but is a text column. I need to convert it to the date column for which I'd normally use the DATEVALUE() function. However, because my language and formatting settings are not US, Excel will show the #VALUE! error.
Now I could go and change my system settings, maybe even Excel settings (I think I've seen date and number format settings there) but I'd much rather solve this issue using universal Excel functions only. I.e., I don't want to move to another computer, possibly with other locale settings, and have to worry about this again.
Do I need to do some kind of text parsing?
Best Answer
Borek, I believe the following may solve the issue
=DATE(RIGHT(RefCell,4),LEFT(RefCell,FIND("/",RefCell)-1),MID(RefCell,FIND("/",RefCell)+1,FIND("/",RefCell,FIND("/",RefCell)+1)-FIND("/",RefCell)-1))
I assumed the delimiter is "/" based on your description and that the year is formatted as YYYY. I hope this does the trick.