Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Due date calculation
Microsoft Dynamics NAV (Archived)

Due date calculation

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

Hi,

I have to calculate a due date in the payment terms, and it should be calculated like this:

Add 1 month, and from that date, take the 10th of the month or, if we end after the 10th, take the 20th of the month.

Example: posting date = 02/06/2015 then the due date should be  10/07/2015. 

               posting date = 11/06/2015 then the due date should be 20/07/2015 

I have been making many exotic combinations for calculation of due dates, but this one I don't see how to do it.

As my customer says it is a common used practice in France and Spain, maybe somebody already found a way to do it ?

kr,

Francis

*This post is locked for comments

  • Suggested answer
    Vytenis Jakas Profile Picture
    Vytenis Jakas 70 on at
    RE: Due date calculation

    Hi Francis,

    I am afraid your requirement cannot be fulfilled with standard NAV date formulas, but it could be a quite simple modification to implement.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Due date calculation

    Hi Alex,

    I am working in the sales area to let the application calculate the dur date of an invoice, just using the standard functionality.

    I would like to stick to the standard functionality. I think the only option will be changing the payment conditions or making some modifications to the calculation.

    kr,

    Francis

  • Suggested answer
    Alex A Profile Picture
    Alex A 2,348 on at
    RE: Due date calculation

    I have worked with dates in many languages (C#, ASP, javascript, VBA, SQL) and the logic/algorithms are always the same. If you don't have conditional statements and date functions in the language you are working with then you may need to change your approach. Are you working with a report, with a form in NAV, or what?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Due date calculation

    Have you ever made a date calculation that contains a condition using regular code??

    june 15th: -CY+5M+14D

    1 year: +1Y-1D

    aug 15:-CY+7M+15D

    see you can figure out pretty much any date but when you add in <10th of the month "do this" else "do that" you'll run into issues.

  • Alex A Profile Picture
    Alex A 2,348 on at
    RE: Due date calculation

    Hi Francis, your standard functionality should include date formulas that allow you to work with dates in the same way that I have shown. The logic I provided you is an example of how this can be done with date functions using any language.

    Are you trying to calculate this date for a custom report or for a custommized document in NAV?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Due date calculation

    Hi Alex,

    I know it can be solved by programming, but I would like it to be solved with the standard functionality using the date formulas.

    kr,

    Francis

  • Suggested answer
    Alex A Profile Picture
    Alex A 2,348 on at
    RE: Due date calculation

    Francis, Here is the logic for your question presented in TSQL. The logic would be similar in any language.  You could run this query against your NAV database using SQL Management Studio, Visual Studio, or any query writing application to see how it works. Also, look at this screen shot to see the result:

    Note: Replace the XXXXXX with the prefix from your database.

    tempdelete2.png

     

    SELECT
CONVERT(VARCHAR, [Posting Date], 100) AS [Posting Date],
CONVERT(VARCHAR, CASE
    WHEN DAY([Posting Date]) <= 10 THEN DATEADD(mm, 1, DATEADD(dd, (10 - DAY([Posting Date])), [Posting Date]))
    WHEN DAY([Posting Date]) <= 20 THEN DATEADD(mm, 1, DATEADD(dd, (20 - DAY([Posting Date])), [Posting Date]))
    WHEN DAY([Posting Date]) <= 31 THEN DATEADD(mm, 1, DATEADD(dd, (20 - DAY([Posting Date])), [Posting Date]))
    ELSE [Posting Date]
    END , 100) AS [New Date]

FROM [XXXXXX$Sales Invoice Header]

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