Date format when used with CONCATENATE

Phil_Waite · 08-30-2020 05:24 AM

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

Steve

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.

Phil_Waite

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

Aleksi

@Phil_Waite And you can’t use 2020/08/20 -----south?

Phil_Waite

Hi Aleksi,

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

Phil_Waite

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

WillowMobileSys

@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.

Aleksi

You could do that like TEXT([Date],“yyyy/mm/dd”)&" -----south"

Phil_Waite

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

Steve