Problem formatting the date in imported data

[roxannewarren83]

Hi!
I'm wondering if someone can help? I'm having the following problem:
Importing data from our accounts software. Date column is showing as JAN/18 (for example) I need to convert it to Jan-18. The only way I can make this work is to overtype it as I want but then I can't copy this down a whole row of data with different dates obviously. If I go into custom format and select the mmm-dd option it just doesn't do anything. It's coming through as 'txt' which I'm assuming is part of the problem? Is there a way to convert it to date and then into the right format so I can change the whole column in one go? Hope that makes sense! Thanks in advance

[63falcondude]

Hello and welcome to the forum.

You can convert the text date into a real date by using a formula like this (let's say in B2):

=EOMONTH(A2+0,-1)+1

Copy the formula down column B and then you can wither leave it there or copy and paste as values back into column A.

Once you have a real date in there, you can format it however you'd like.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

If it really is text in the way you describe then you'll need a either a two column lookup table or the equivalent lookup in the formula to convert he text 'Jan' to a month number. Then a

Please Login or Register  to view this content.

However as with so many of these things this is a much simplified example and might not represent all your data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook as mentioned

[roxannewarren83]

Amazing thanks so much this has solved the problem!

[63falcondude]

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[roxannewarren83]

Ah thanks I was wondering how to do that as I could see other threads marked as solved - will do now, thanks again