Date issue in VC

[ETS]

Hi,

I have a date column with the format DD/MM/YY. I want a VC column with the format of year-month-week

week means first, second, third, fourth

[LeventK]

@ETS
You can try with this expression. I propose to append “W” letter for the week number to prevent the user mixing it with a YYYY/MM/DD format.

TEXT(
    [DateColumnName],
    YEAR([Date])&"-"&RIGHT("0"&MONTH([Date]),2)&"-W"&RIGHT("0"&WEEKNUM([Date]),2)
)

[ETS]

If the date is 8/11/20 Then I want to get it like - “November-Second week”

[LeventK]

@ETS

---

CREATE A VC AND NAME IT AS "UNITS"

---

IFS(
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="1","First",
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="2","Second",
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="3","Third",
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="4","Fourth",
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="5","Fifth",
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="6","Sixth",
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="7","Seventh",
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="8","Eighth",
	RIGHT(CONCATENATE(WEEKNUM([Date])),1)="9","Nineth"
)

---

CREATE A VC AND NAME IT AS "TENS"

---

IFS(
	LEN(CONCATENATE(WEEKNUM([Date])))=2,
	IFS(
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0"),"Tenth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="1"),"Eleventh",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="2"),"Twelveth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="3"),"Thirteenth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="4"),"Fourteenth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="5"),"Fifteenth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="6"),"Sixteenth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="7"),"Seventeenth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="8"),"Eighteenth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="1",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="9"),"Nineteenth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="2",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0"),"Twentieth",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="2",NOT(RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0")),CONCATENATE("Twenty-",[Units]),
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="3",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0"),"Thirty",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="3",NOT(RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0")),CONCATENATE("Thirty-",[Units]),
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="4",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0"),"Fourty",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="4",NOT(RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0")),CONCATENATE("Fourty-",[Units]),
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="5",RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0"),"Fifty",
		AND(LEFT(CONCATENATE(WEEKNUM([Date])),1)="5",NOT(RIGHT(CONCATENATE(WEEKNUM([Date])),1)="0")),CONCATENATE("Fifty-",[Units])
)

---

CREATE A VC FOR TEXTIFYING YOUR DATE AS REQ’D

---

SWITCH(
	MONTH([Date]),
	1, "January",
	2, "February",
	3, "March",
	4, "April",
	5, "May",
	6, "June",
	7, "July",
	8, "August",
	9, "September",
	10, "October",
	11, "November",
	"December"
) & "-" &
IFS(
	LEN(CONCATENATE(WEEKNUM([Date])))=1,[Units]&" week",
	LEN(CONCATENATE(WEEKNUM([Date])))=2,[Tens]&" week"
)

It’s possible to concatenate all these under a single expression if you want, but I always tend to separate complex expressions into fractional parts as I find it more manageable for the sake of debugging.