1

I'm using the following formula to retrieve week day and day from a Google Analytics report in Google Spreadsheet:

=CONCATENATE(CHOOSE(WEEKDAY('90diaspessoas'!A16),"Domingo","Segunda","Terça","Quarta","Quinta","Sexta","Sábado")," ",TO_TEXT('90diaspessoas'!A16 ) )

It returns to me a value like this:

Sábado 2017-12-02

Where Sábado means Saturday, the problem is that the date is being displayed with this format YYYY-MM-DD and I need it to display like this DD-MM-YYYY, I tried using the following but it didn't work:

=CONCATENATE(CHOOSE(WEEKDAY('90diaspessoas'!A16),"Domingo","Segunda","Terça","Quarta","Quinta","Sexta","Sábado")," ",TO_TEXT(DATE(LEFT('90diaspessoas'!B16,4),MID('90diaspessoas'!B16,5,2),RIGHT('90diaspessoas'!B16,2)) ))

This is what I'm getting: Sábado 12/31/2030

The table: enter image description here

1

You need to use TEXT, instead of TO_TEXT:

=TEXT('90diaspessoas'!A16,"dd-mm-yyyy" ) 

As long as underlying date number is correct, This will work.

OR

Set formatting in '90diaspessoas'!A16 correctly to DD-MM-YYYY. Then, TO_TEXT will work.

FINAL CONCATENATED FORMULA:

=TEXT('90diaspessoas'!A16,"dddd dd-mm-yyyy")
  • Wow, that's amazing. Can you explain me how did you manage to join both data without using CONCATENATE term? – 023023 Mar 2 '18 at 17:57
  • Wait, there is a problem here. It's in English. I need it to be in Portuguese... – 023023 Mar 2 '18 at 17:58
  • Can I use it like this instead? =CONCATENATE(CHOOSE(WEEKDAY('90diaspessoas'!A16),"Domingo","Segunda","Terça","Quarta","Quinta","Sexta","Sábado")," ",TEXT('90diaspessoas'!A16,"dd-mm-yyyy")) – 023023 Mar 2 '18 at 18:01
  • Did what you suggested, but had to change language too and make functions appear in Portuguese. Had to revert, I'm afraid that if I leave the functions in Portuguese, everything may end up messed up.. – 023023 Mar 2 '18 at 21:13
  • 1
    Wait, looks like it was a cache thing. Changing the locale solved. thx – 023023 Mar 2 '18 at 21:16

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.