Re-format text date dd.mm.yy

[lapta301]

Dear All

My apologies for posting yet another date format question but I have spent ages looking for this without any success. Sorry if I have missed a previous post.

Basically a lot of people here enter dates in the format dd.mm.yy which are actually text values.

What I would like if possible is 2 resolutions to cover various situations that occur firstly a formula to convert the text into a proper date dd/mm/yy and also a macro to scan through a range of cells selected by me.

As ever any help that you can provide is most appreciated.

Regards from a soaking wet Southern England

 

[squidgeny]

If a date is stored as text, converting it to an actual date is going to be very tricky. Is there no way you can use Data Validation to force your colleagues to enter an actual date?

PS. and you be glad you're in the soaking wet south - it's been far too hot up here in the west midlands :P

 

[lapta301]

squidgeny

Unfortunately I'm trying to correct a few years of historical data.

I agree about the weather its been very hot here over the last few days, mind you its still quite warm just very wet.

 

[Weaver]

For the second part (where you correct the data)

http://www.mrexcel.com/forum/showthread.php?t=568532

 

[Norie]

It can actually be quite straightforward to do this sort of thing.

It's unclear though if you want to do it using formulas, code or code and formulas.

Also do you want a one off solution to deal with the historical data and an ongoing solution that can be used when needed.

The reason that's a bit confusing is that you seemed to say data validation wasn't really an option.

That or some code that "fixes" the input when it's actually entered would probably be a good idea.

 

[R Pelle]

Probably won't fix all your historical data but why not try a simple Replace . with / as a start to resolving your issue(s).

 

[Norie]

A very simple formula that will only really deal with the exact format dd.mm.yyyy is something like this.

=DATEVALUE(SUBSTITUTE(A4,".","/"))

R Pelle's find/replace does the same but can be used for whole ranges.

It also doesn't use a formula which is probably a good idea - you don't want loads of formulas.

 

[lapta301]

Dear All

Apologies for the delay in responding to your suggestions.

To put it mildly I feel a right *** as in most of the cases that I have tried the search and replace method suggested by R Pelle works fine.

Why do I always ignore the simplest method.

I'm wading throught the historical batch slowly to make sure that it doesn't bite me on the tail but so far its looking good.

My sincere thanks to you all for taking the time. I will keep all of your suggestions to hand.