Datevalue function does not work for German Language Excel using English dates

  • Firstly I apologise for the cryptic title, could not really think of a succinct way to describe the issue. So to the longer explanation.

    I have created a excel template that simply generates some graphs etc for a monthly report. Each month data from a website is cut a pasted to one sheet, and then the information is converted to the correct units etc, and some graphs are updated.

    The issue is I am working in Germany, so my colleagues use the template with a German Excel version. The data input is English (US) from the consultancy website. Part of what has to happen is that the pasted data contains dates of the format Jan-2011, Feb-2011 etc etc these are actually pasted into Excel as text rather than dates. I then use the Datevalue function to convert to a date, and finally put them in the format J11, F11 etc for the axis on the graphs. But if you have a German version, it will expect you to have Mai instead of May, or Okt instead of Oct for example and therefore leads to a #Value error etc.

    Has anyone else come across this and found an easy solution? Perhaps replacing the text, although i am not sure how to go about that at this point. Thanks

  • Re: Datevalue function does not work for German Language Excel using English dates

    Hi Herbds7,

    Sorry not sure I follow you. The =Text function takes a date and converts to Text??? I have text and want a date, or am I wrong. As for the 2nd part - not sure how to use that but I shall take a closer look, thanks for taking the time.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!