Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / How to convert a calcu...
Small and medium business | Business Central, N...
Suggested answer

How to convert a calculated date in a specific way?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello everybody,


I have the following problem:

I have three fields, a start date, an end date and a duraton field.

The user enters a value in the start date and end date (01.01.2020 to 31.12.2020 and will have a value calculated in the third field.

Currently, however, there are only days for me...how can I generate a value from it, the days months and It should look like this: for example, I get 365 days as a result from the difference between the start date and the end date, but I don't want to show 365 days in the field "duration", but 30 days and 11 months.

Do you know anything like that?

  • Suggested answer
    ArturV Profile Picture
    ArturV 225 on at
    RE: How to convert a calculated date in a specific way?

    You can find the code snippet from this thread to calculate the days separately:

    community.dynamics.com/.../duration-datatype-in-reports

    Then you can get the months from the days. When you get both days and month, you won't be able to show this information in the duration field but rather in the text field after this kind of format:

    STRSUBSTNO('%1 days and %2 months',Days,Months);

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to convert a calculated date in a specific way?

    I have already tried this by taking the end date and subtracting the start date from it. I did that with the function DATE2DMY for day, month and year. That worked in most cases, but if I have an end date of 01.03.2021 and a start date of 11.06.2019 then I get the following: 01-11 = -10 / 03-06 = -3 / 2021-2019 = 2 = -10.- 03.2 I won't get any further with the ABS function, because the result is still not correct ... the result has to be: 19 days 9 months and 1 year. Do I have to work with rounding-functions?

  • Suggested answer
    Jyotsna NAV Profile Picture
    Jyotsna NAV 585 on at
    RE: How to convert a calculated date in a specific way?

    Hello,

    You can first use DATE2DMY and then once you get the months of those dates, you can easily concat and know the number of months and if you know the months, you can easily calculate the days or the duration.

    Regards

    Jyotsna

    jyotsnanav.blogspot.com

  • Suggested answer
    TeddyH Profile Picture
    TeddyH 12,868 Super User 2024 Season 1 on at
    RE: How to convert a calculated date in a specific way?

    I don't think duration do months. You will need to do it manually.

    Duration is basically milliseconds saved in integer.

    You can do DIV and MOD to get the number of months and the remaining.

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!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans