CALCDATE - getting date

roshini · 2008-12-05

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 · 2008-12-05

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 · 2008-12-05

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

Thanks for ur detailed explaination.

roshini · 2008-12-10

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 · 2008-12-10

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 · 2008-12-10

@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 · 2008-12-11

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 · 2008-12-11

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

roshini · 2008-12-11

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 · 2008-12-11

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

CALCDATE - getting date

Comments

Categories