2

I have a column (col. I) with dates that are in different formats: 

16. Jul 98 // 22-MAR-2016 // 04-MAY-2015 //
17. Jul 10 // 13-DEC-2011 // 25. Aug 98  // 12. Sep 11

I used the following code to change the format: 

Sub numberformats()
    Range("I:I").NumberFormat = "dd. mmm yyyy"
End Sub

However, while this worked fine on the cells with format dd. mmm yy, it did not work on the cells with format dd-mmm-yyy. Changing the format with a simply right-click - format cell did not help neither (this was my very first step, before moving over to VBA).

I did an internet search, but all answers I found on similar problems were far too complex for me to adjust it to my problem. I'd be very glad to get some advise (I'm an absolute beginner with this...).

ManishChristian
  • 3,759
  • 3
  • 22
  • 50
Spring2017
  • 21
  • 1

1 Answers1

0

This happens, when you are using a non-English version of Excel.

E.g., in the German version, 22.03.2016 is presented as 22.Mrz 16. Thus, in your case, Excel does not understand that you are giving a date, when you give 22-MAR-2016. The UPPERCASE is a bit problematic.

Probably it is a good idea to change it to 22-Mar-2016 and try again, in my Excel it gets it quite ok.

To see what your Excel understands, go to File>Options>Advanced>User defined lists. There you will see the following: enter image description here

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Oh, thank you very much! I see, yes, my excel is in German. I checked the user defined lists and there is indeed no English version of the months. I added the English ones, but this did not help to get the VBA working. Do I need to do more than adding it there...? I cannot manually change all the upper case, there are too many I get an updated list with these dates regularly, so I would need to find a permanent solution ie a working VBA. – Spring2017 Mar 22 '17 at 14:39
  • Replace `JAN` to `Jan`, `FEB` to `Feb` etc everywhere and it should work. You can automatize the replacement as well. – Vityata Mar 22 '17 at 14:42
  • hm, I see, let me see whether I'm able to do that. thank you very much. – Spring2017 Mar 22 '17 at 16:14
  • You just need 12 times replace. See here - http://stackoverflow.com/questions/8550240/access-vba-how-to-replace-parts-of-a-string-with-another-string or here http://stackoverflow.com/questions/8571192/access-vba-to-trim-space-around-a-specific-character-or-wordss-in-a-column/8571442#8571442 for example. – Vityata Mar 22 '17 at 16:17