WEEKNUM comparing CONCATENATEd date

[jonbowles84]

Hi, I am having trouble getting a WEEKNUM expression to recognise a date value...
My formula for the date value [Invoice Date This Month] is 

CONCATENATE([Invoice Date]&"/"&MONTH(TODAY())&"/"&YEAR(TODAY()))

[Invoice Date] is just a number "25"

Then im trying to use WEEKNUM([Invoice Date This Month]) but it just shows 0
Any tips?
Thankyou!

[Suvrutt_Gurjar]

Could you mention the column types of [Invoice Date This Month] and [Invoice Date] you currently have?

[jonbowles84]

[Invoice Date This Month] is Date

 [Invoice Date]  is Number

[Suvrutt_Gurjar · Accepted solution]

Thank you. Please try below.

Please make [Invoice Date This Month] column as TEXT type and expression as below

CONCATENATE(MONTH(TODAY())&"/"&[Invoice Date]&"/"&YEAR(TODAY()))

Then please have the following expression in the  third WEEKNUM number type column

WEEKNUM(DATE([Invoice Date This Month]))

Typo: I have replaced inadvertently mentioned [Invoice Date] column with WEEKNUM number as underlined and highlighted in italics above.

OR second alternative

if you wish to retain [Invoice Date This Month] in DD/MM/YYYY format, please continue to have that column as date type with your original expression 

CONCATENATE([Invoice Date]&"/"&MONTH(TODAY())&"/"&YEAR(TODAY()))

However for the third WEEKNUM column your expression can be

WEEKNUM(DATE(CONCATENATE(MONTH(TODAY())&"/"&[Invoice Date]&"/"&YEAR(TODAY()))))

Edit: Corrected a typo and added the second alternative.

[shahappsheet]

I just would like to highlight my experience that if Date is declared as text, it created issue in ordering, if it is required to order the data based on the date, so far I saw.

[jonbowles84]

Thankyou! annoying the date is recognised as DD/MM/YYYY...

[Suvrutt_Gurjar]

Yes, the internal AppSheet processing seems to need dates in MM/DD/YYYY format when you pass the dates as text strings to the other AppSheet functions.