How to converte a Date to a text

AmalmMahmoudi · 2025-03-30T23:08:41+00:00

There was an error rendering this rich post.

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

Accepted answers

All comments

devrathahuja

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.

alexpavel

@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

pyrypeura

I agree wtih @alexpavel using RIGHT instead of IF statement helps on getting more readable solution.

Dikshant

I usually append a zero and use RIGHT function to pick 2 digits to transform day with one or two more digits

Quick Links

Can you help?

Unanswered questions

Importing Ragged Hierarchies
Hello - I have a 2 level ragged hierarchy (yes, it's a chart of accounts), that for reporting purposes is used in 4 different models (yes, it's a P&L). What is the best way set this to allow one to be the "source of truth" and then import between the four models without creating actions that include ignores? It seems like…
Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…