Getting error in PowerFx formula to cancluate difference in 2 dates

(0) Share

Report

Posted on by rohitK264

I am trying to calculate difference between 2 date fields to calculate number of years, no of days and hours. For this I have created a field with data type formula and used below formula:

Concatenate(DateDiff('Today Date',Text(Birthday,DateTimeFormat.LongDateTime24), TimeUnit.Years),/ Y /, DateDiff('Today Date',Birthday,TimeUnit.Days), / D /, DateDiff('Today Date',Birthday, TimeUnit.Hours)/60, / H /)

But I am getting an error in formula as: /Use the Text function to convert numbers to text. Include a format in the second argument, which cannot include a thousands or decimal separator in formula columns./ even after using the text function as mentioned in the documentation.

Can anyone help me in correcting this formula?

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Dengliang Li Microsoft Employee on at

Like (1)

Report

Hi,

That is an indication that you need to add the second parameter of the Text function.
The second parameter is used to determine the format in which the number will be displayed.

Text function - Power Platform | Microsoft Learn

This is my PowerFx column that shows the difference between two date fields.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more.
If you have further questions, please feel free to contact me .

Best Regards,
Dengliang Li

Was this reply helpful? Yes No
rohitK264 10 on at

Like (0)

Report

Hi Dengliang Li,

Thanks for your answer. It solved the error.

But the issue I am facing now is it is calculating number of days between those 2 dates like 24Years10142Days. What I want is I want to display how many days are past above 24 years like 24Years2Days or 24Years350Days likewise.
Is is possible through this expression ?

Was this reply helpful? Yes No
Dengliang Li Microsoft Employee on at

Like (1)

Report

Hi,

You can use the Mod function to get the number of days remaining after dividing the total number of days by 365.
Mod function - Power Platform | Microsoft Learn

Best Regards,
Dengliang Li

Was this reply helpful? Yes No
rohitK264 10 on at

Like (0)

Report

Hi Dengliang Li,

Thanks a bunch.

Was this reply helpful? Yes No