Skip to main content
Microsoft Dynamics NAV forum
Suggested answer

How to convert a calculated date in a specific way?

editSubscribe (0) ShareShare
ReportReport
Posted on by UG Leader

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?

Categories:
  • 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 UG Leader 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,720 Super User 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.

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,045 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,570 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans