Google-sheets – Format Google Analytics Date

dateexcel-onlinegoogle sheetsgoogle-sheets-chartsmicrosoft excel

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

Best Answer

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")
Related Topic