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

Improve this question

1 Answer 1

Reset to default
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")
Improve this answer
5
  • Wow, that's amazing. Can you explain me how did you manage to join both data without using CONCATENATE term?
    – 023023
    Mar 2, 2018 at 17:57
  • Wait, there is a problem here. It's in English. I need it to be in Portuguese...
    – 023023
    Mar 2, 2018 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, 2018 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, 2018 at 21:13
  • 1
    Wait, looks like it was a cache thing. Changing the locale solved. thx
    – 023023
    Mar 2, 2018 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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