Date format when used with CONCATENATE

Phil_Waite
Participant V

Hi,

I have a date column that works fine. It is formatted dd/mm/yyyy.

When I CONCATENATE the date column with other columns it reverts to mm/dd/yyyy. (I am in the UK)

I can use CONCATENATE(TEXT([Date] etc which I have used before to get the right format but I am using the CONCATENATED column in the โ€œgroup byโ€ function and with the TEXT funstion the order is ot according to the date.

Below is a pic of the correct order but wrong date format

Below is the correct format but wrong order

Thanks

Phil

0 9 1,042
9 REPLIES 9

Steve
Participant V

What you want to do is not possible. If you want to sort by date and want the date format localized, the date must be the only thing in the grouping column and the column must be of type Date.

Hi Steve,

Thatโ€™s frustrating as itโ€™s really helpful to have more than just the date in the โ€œgroup byโ€ heading as you can see from the pics.

I will try and play with what is concatenated. Maybe have another virtual column with a number assigned to each date in order and concatenate it at the begginning of the date----southโ€”no of casks etc

so it reads

1 02/09/2020 -----south

2 03/09/2020 ----- south

I will have a number in front that means nothing to the user but not a big issue

@Phil_Waite And you canโ€™t use 2020/08/20 -----south?

Phil_Waite
Participant V

Hi Aleksi,

How would I get the date format reversed like that? Would the TEXT function not revert it to mm/dd/yyyy still?

Does anyone know how the order is being chosen when it is in text format - i canโ€™t see why the order is as it is.

thanks

@Phil_Waite Once the date is part of a string, the sorted order is simply string based. This means that in dd/mm/yyyy format the strings with the day = โ€œ03โ€ will be all together. That is probably not what you want? You probably want all the days of the same month in day order then all the days of the next month in day order?

I had the same problem for a client in London but with weekly Group headers. I solved by including the Week # in header text.

For your case, youโ€™ll need to insert something to force the sort order such as what @Aleksi suggested or some other meaningful info that results in the order you wish.

You could do that like TEXT([Date],โ€œyyyy/mm/ddโ€)&" -----south"

Hi

I have moved the TEXT9[Delivery date to the end to give the correct format and kept the reverted US format at the start - sales team just have to look at the end rather than the start of the line.

IF([Route]=โ€œAโ€, CONCATENATE([Delivery Date],"-----North-----",โ€œNo. of Casks โ€œ,[No. Of Casks],โ€-----โ€,TEXT([Delivery Date])),

CONCATENATE([Delivery Date],"-----South-----",โ€œNo. of Casks โ€œ,[No. Of Casks],โ€-----โ€,TEXT([Delivery Date])))

Thanks

Phil

Top Labels in this Space