1

I want a cell to display the current date and some string in addition to it. All formatted as a string e.g. 12.02.2020 My, Name _______ I tried to use TEXT() but the formula =TEXT(TODAY();"dd/mm/yyyy")& "; My, Name _______" does yield a VALUE!-Errormessage. I think it is because my excel isn't interpreting my formatting in this case "dd/mm/yyyy" the right way.

I use Office 2016.

The solution was as suspected in my local settings. My excel is set on english but the local settings are set to german. So the right formatting was tt/MM/JJJJ.

  • You need to be looking at the concatenate function. – spikey_richie Feb 12 at 15:51
  • 1
    Does your local setting use ; or , as the delimiter? – Scott Craner Feb 12 at 18:24
  • what happens when you replace "dd/mm/yyyy" with other setting options? What can you make work? – Alex M Feb 12 at 18:29
  • 1
    You may have tokens other than m,d,y for month, day , year. Check your numberformat options and also your Windows Regional Settings. – Ron Rosenfeld Feb 13 at 2:46
  • no need to edit your question to include the answer. the answer is in the answer. – Alex M Feb 13 at 20:40
3

Check your locale setting,...

Example:
English locale =Text(Today();"dd/mm/yyyy")
must be written =Text(Today();"dd/mm/åååå") for a Swedish locale (Year = År)

... or even worse; with an Excel having Swedish text in the menus: function names may have been translated; today() could be idag()

I Avoid this as the Plague by only ever installing the ENGLISH version of Office

  • That was super weird. I have german locale settings so tt/MM/JJJJ works but my excel is completely in english, so I never thought of that! – Lucas Raphael Pianegonda Feb 13 at 7:49
3

You are pretty much there.

Try:

=TEXT(TODAY();"dd/mm/yyyy")&" Some rrnadom text here"

You could also use CONCATENATE as suggested by Spikey:

=CONCATENATE(TEXT(TODAY();"dd/mm/yyyy");" Random Text";" More Randoms")

You may need to swap ; for , depending on your locale.

  • I am sure this is how it is supposed to work. But I still get an Value!-Error, I think it is because of the date-format specifier dd/mm/yyy. I tried only =Text(Today();"dd/mm/yyyy")and it yields the error. – Lucas Raphael Pianegonda Feb 12 at 16:22
2

CONCATENATE will solve issue =CONCATENATE(TEXT(TODAY(),"dd/mm/yyyy")," ", "yourtext)

  • I am sure this is how it is supposed to work, but I still get the Value!-Error. I think "dd/mm/yyyy" is the issue. – Lucas Raphael Pianegonda Feb 12 at 16:09
  • Dear i checked formula in excel . Could you please send exact formula did you try – mohammad moeini Feb 12 at 16:10
  • =CONCATENATE(TEXT(TODAY();"DD/MM/YYYY");" "; "yourtext") as I have a local delimiter ; instead of , – Lucas Raphael Pianegonda Feb 12 at 16:12
  • Dear , works for clear parameterand ; its not working at them . If you have ; in text or data is better have seprate column for data and your text and concat them with formula as below =CONCATENATE(TEXT(A2, "yyyy-mm-dd")," ", B2) – mohammad moeini Feb 12 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.