ENCODEURL([Start Date]) in MMDDYYYY format

[1minManager]

So I’m using ENCODEURL([Start Date]) in a workflow as part of a URL string that does a webhook to an SMS provider. The issue is I’m in the UK and its displaying in the SMS as MMDDYYYY and not DDMMYYYY. The date is in UK format in the App and the same in the spreadsheet. I’ve tried doing ENCODEURL(DATE([Start Date])) and changing the spreadsheet column formating from Date to Text. Both made no difference.

Does anyone have any suggestions. Or do I have to create a CONCATENATE() equation to force the issue

[Steve · Accepted solution]

If [Start Date] is already a Date, DATE([Start Date]) is redundant and unnecessary.

TEXT([Start Date]) will format the Date value according to your locale and whatever format-affecting settings the Start Date column has (e.g., Use long date format).

The TEXT() function can also be given a format string to format Date (or Time or DateTime) values exactly as you want.

help.appsheet.com

TEXT()

Format as text

[1minManager]

Hi @Steve

I’ve investigated further since some dates in the same report are showing correctly whilst others were in the wrong locale. It seems if the date is inside a CONCATENATE() function then it shows as MMDDYYYY whereas if its just <<[Start Date]>> it displays as DDMMYYYY.

<<IF(AND([Status]=“Booked”,[Latest Qualification]=TRUE,[Valid Until]>Today()+365),CONCATENATE([Valid Until],” - “,[QLLongText]),"")>>
Showed - 09/16/2021 - Medium Risk Confined Space

<<IF(AND([Status]=“Booked”,[Latest Qualification]=TRUE,[Valid Until]>Today()+365),CONCATENATE(TEXT([Valid Until]),” - “,[QLLongText]),"")>>
Showed - 16/09/2021 - Medium Risk Confined Space

<<IF(AND([Status]=“Booked”,[Latest Qualification]=TRUE,[Valid Until]>Today()+365),CONCATENATE(TEXT([Valid Until]),” - “,[QLLongText]),"")>><<[Valid Until]>>
Showed - 16/09/2021 - Medium Risk Confined Space16/09/2021

Not sure if you classify this as a bug or not? But its not intuitive.

Simon@1minManager.com

[Steve]

Looks right to me.

TEXT([Valid Until]): When wrapped in TEXT(), the Date-type value is converted to its display format–that’s explicitly what TEXT() does.

<<[Valid Until]>>: Referencing a raw Date-type value in a template also uses the display format, as the template is a display view.

CONCATENATE([Valid Until], ...): referenced raw within an expression, the Date-type value remains in its raw format. The concatenated result is no longer recognized as a Date value and so receives no display formatting.