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?

All responses (4)

Answers (1)

rohitK264 10 on at

Like (0)

Report

Getting error in PowerFx formula to cancluate difference in 2 dates

Hi Dengliang Li,

Thanks a bunch.
Dengliang Li Microsoft Employee on at

Like (1)

Report

Getting error in PowerFx formula to cancluate difference in 2 dates

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
rohitK264 10 on at

Like (0)

Report

Getting error in PowerFx formula to cancluate difference in 2 dates

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 ?
Verified answer

Dengliang Li Microsoft Employee on at

Like (1)

Report

Getting error in PowerFx formula to cancluate difference in 2 dates

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