Refreshing/Recalculating dates in spreadsheet

Right, I've tried to find solution for days now, without any success, so I hope there's some guru in here who already encountered that problem and will be kind enough to provide a solution.
So, long story short:
I have three columns, in first there's date in unusable format (22.02.2017/13:23:19), in second there's formula to extract actual date "=LEFT($E3;(LEN($E3)-(LEN($E3)-FIND("/";$E3))-1))" and then I copy second column, and paste it 'as values' to the third one. All cells in third column are formatted as date, tried in every possible combination. And the thing is, excel doesn't see the third column as a date, until I enter every cell, and press enter, even without any change to the data in this cell. That also works with ctrl+h 'find and replace' tool - I just put there find '.' replace with '.' (so again, no change at all) and all cells will 'refresh' and excel will recognize values in them as dates.
The actual problem is, that now when I got tired of doing same stuff over and over again, I've made macro for it...and it doesn't work. I mean it works, so it replace what I want it to replace, but it somehow lost the 'refresh ability', so excel still doesn't recognize values as dates, unless I do that manually.

Does anyone have a solution, how can I make excel to recalculate/refresh/whatever those cells so it'll see them as dates?

Thanks,
FFLORO

I presume you are using a non-English version of Excel, as you show semicolons in your formula rather than commas. You can use this formula to derive the date:

=DATE(MID($E3;7;4);MID($E3;4,2);LEFT($E3;2))

and apply an appropriate format to that cell, then copy down.

Hope this helps.

Pete

That depends, I use English version most of the time, but when I'm working with Rexroth controllers (German company) I change regional settings to Germany - makes it much easier than importing data from .csv files.
Anyway, regarding your formula - it's something I didn't know even exists, it's much more efficient. I get a notification though, that I've entered too few arguments for this function - not a clue why, but I've changed semicolons to commas, regional settings back to UK and it runs exactly as I need it to, thanks! :D

However, just for future reference - formula above solves that particular problem with dates, not really the one with non-refreshing cells. Any ideas about that?

FFLORO

Pete,

I'll mark it as solved, as I don't really have a problem anymore. However, you've explained how excel works with those cells (which I appreciate, it'll make easier to solve future problems without using forums ) but the original question still stands - if by using Find & Replace function I forced Excel to try to make sense of the value in the cell, how can I force it to do so within a macro? That tool didn't work there anymore, and I can't find any way to do so except manually entering cells.

Cheers,
FFLORO