CALCDATE - getting date

roshini

Hi Nav Experts!

I am using this formula to get duedate,

payment2:= CALCDATE('<CM+D'+(duedate)+'-1M>', "Posting Date");

for Example: if due date = 31 and posting date = 08/05/10(YY/MM/DD)

then result showing 08/05/30, but result shold be 08/05/31.

what is the wrong in my code ??

Kindly suggest correct one.

Thanking You

DenSter

Let's look at your calcdate:

payment2:= CALCDATE('<CM+D'+(duedate)+'-1M>', "Posting Date");

• CM -> takes you to the last date of the current month
• +D -> you don't specify how many days, but I am assuming you want to go to the first date of next month
• + duedate -> This is what screws you up. only dateformulas are accepted in here. If you need to construct a new date using a 'day number', you need to use the DMY2DATE function
• -1M going back one month, so now we're at the first date of the current month

You can go to the first date of the current month by using <-CM>. One other tip would be to take it in steps, that always helps me figure out what to do:

DummyDate := CALCDATE('<-CM>',"Posting Date");
FinalDate := CALCDATE('<+5D>',DummyDate);

What you seem to need though is not adding a number of days, but to construct the date with a day number into the month/year of the date parameter. This is a little complicated because not all months have a day number 31, or even 30, so you'll have to build some logic around it. The function you are looking for though is the DMY2DATE function. Check out the C/SIDE reference guide (in the help menu) for more details.

roshini

Thanks a lot Mr. DenSter as I came to know more knowledge on CALCDATE.

Thanks for ur detailed explaination.

roshini

Mr.DenSter

+ duedate -> This is what screws you up. only dateformulas are accepted in here. If you need to construct a new date using a 'day number', you need to use the DMY2DATE function

Not getting date,

showing error Invalid date.

How can I get if duedate=15, then that month 15(15/08/08), if duedate =20 then that month 20(20/08/08) ??

Sandeep_Prajapati

How can I get if duedate=15, then that month 15(15/08/08), if duedate =20 then that month 20(20/08/08) ??

Hi roshini,
If I m getting u correctly then

payment2 := DMY2DATE(duedate,DATE2DMY("Posting Date",2),DATE2DMY("Posting Date",3));

DenSter

@Roshini: you may not know about this, but there is extensive help available for C/AL keywords such as DMY2DATE. Go to the Help menu, and select C/SIDE Reference guide. In there you will see a search tab. Enter the keyword in there and you will find an explanation of the parameters, return values, and sometimes even code examples.

roshini

Hi Sandeep,

payment2 := DMY2DATE(duedate,DATE2DMY("Posting Date",2),DATE2DMY("Posting Date",3));

Big Thanks!

now it is working as desired!


Hi Mr. DenSter

Thanks for your advise, as first I searched help only but I couldn't get help as expected, got help for normal cases, and I don't hv that much exposure. Thanks for your guidence.

Thanking you......


I hv one more Issue:

I am adding +1M or NoofSusMonth)+'M' now problem is arising with 30 if payment2 = 30/09/2008 then +2M result is O.K, but if +1M result is showing 30/10/2008, but it should show 31/10/2008.

My code as follows:

SuspenseMonthDue := CALCDATE('<'+(NoofSusMonth)+'M>', payment2);


can I expect any suggest me how to handle ??


Thanking you

DenSter

First day of the month: <-CM>
Last day of the month: <+CM>
Last day of next month: <+1M+CM>

roshini

Hi Mr. DenSter

Thanks for quick reply, as issue is not last day, or first day of month, when ever if duedate comes on 30th how to caliculate exact one month ex: if duedate is 30/09/2008,exact one month will be 31/10/2008, not 30/10/2008.

or I need to write IF condition for 30 ??


Thanking you

DenSter

It is clear that you have not tried my suggestion. Try it first, THEN ask more questions :-k