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!
Solved! Go to 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.
Could you mention the column types of [Invoice Date This Month] and [Invoice Date] you currently have?
[Invoice Date This Month] is Date
[Invoice Date] is Number
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.
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.
Thankyou! annoying the date is recognised as DD/MM/YYYY...
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.
User | Count |
---|---|
35 | |
30 | |
30 | |
20 | |
18 |