Skip to main content

Notifications

Customer experience | Sales, Customer Insights,...
Answered

Getting error in PowerFx formula to cancluate difference in 2 dates

Posted on by 10
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?
 
  • rohitK264 Profile Picture
    rohitK264 10 on at
    Getting error in PowerFx formula to cancluate difference in 2 dates
    Hi Dengliang Li,
     
    Thanks a bunch.
  • Dengliang Li Profile Picture
    Dengliang Li Microsoft Employee on at
    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 Profile Picture
    rohitK264 10 on at
    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 Profile Picture
    Dengliang Li Microsoft Employee on at
    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.
     
    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
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,219 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans