How to converte a Date to a text
Hello,
I am trying to convert a date (Line Item "DLC DATE") to text (Line Item "DLC DATE TEXTE") using this formula:
TEXT(DAY('DLC DATE 2'), "00") & "/" & TEXT(MONTH('DLC DATE 2'), "00") & "/" & TEXT(YEAR('DLC DATE 2'), "0000")
However, I don't get the correct values for the month or day when the value is under 10. For example, I get 2/4/2024 not 02/04/2024.
Can anyone help me to correct this formula, please?
Thanks,
Regards,
AMAL
Answers
-
Hi @AmalmMahmoudi ,
You can do this by identifying the length of the day & month and write a IF condition
IF length day < 10 then 0 & "text(day(date)) else text(day(date) — For day (Similar formula for month) & then concat it all to get the date in required format.0 -
@AmalmMahmoudi : to have leading zeros and avoid IF statements you can use the RIGHT function. See example for DAY:
RIGHT("0" & DAY('DLC DATE 2'),2) .. if the day will be less than 10, it will return 03 … as you concatenate 0 before… if the number is above or equal 10, it will return the same number…
Hope it helps
Alex
2 -
I agree wtih @alexpavel using RIGHT instead of IF statement helps on getting more readable solution.
0 -
I usually append a zero and use RIGHT function to pick 2 digits to transform day with one or two more digits
0
