Small and medium business | Business Central, N...

How to convert a calculated date in a specific way?

(0) Share

Report

Posted on by Community Member

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?

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Teddy Herryanto (Th... 14,300 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Suggested answer

Jyotsna NAV 585 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Suggested answer

ArturV 225 on at

Like (0)

Report
Copy link

Link copied!

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);

Was this reply helpful? Yes No