DateFormula Part 1: Closing Tricks

Any time you have to enter a DateFormula, such as Payment Terms or other places where a value such as “30D” would work, there are a few easy ways you can get the beginning or end of the current Year, Quarter, Month, or even week. Simply use:

For users, this means if you wanted the Payments of Customers always due at the end of the current month, you’d setup a new Payment Term with a Formula of ‘CM’.

For developers, this means you can quickly get the current year into a filter like so:

DateRec.SETRANGE("Period Start",CALCDATE('-CY',WORKDATE),CALCDATE('CY',WORKDATE));

We’ll dive into some of the stranger uses of DateFormula in a later post, including how to factor in Weekdays, or the xth day of the month/week.

[DDET For developers, some sample code]You can test out the above examples with this object.  Save it as as text file, then import, compile, and run.

OBJECT Form 80001 Closing Dates Math Example
{
  OBJECT-PROPERTIES
  {
    Date=04/20/11;
    Time=[ 3:28:41 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    Width=8000;
    Height=8000;
  }
  CONTROLS
  {
    { 1000000002;TextBox;3630 ;220  ;1700 ;440  ;CaptionML=ENU=Date Basis;
                                                 SourceExpr=MyDates[1];
                                                 OnValidate=BEGIN
                                                              Calc;
                                                            END;
                                                             }
    { 1000000003;Label  ;220  ;220  ;3300 ;440  ;ParentControl=1000000002 }
    { 1000000004;TextBox;3630 ;1100 ;1700 ;440  ;CaptionML=ENU=-CY;
                                                 SourceExpr=MyDates[2] }
    { 1000000005;Label  ;220  ;1100 ;3300 ;440  ;ParentControl=1000000004 }
    { 1000000006;TextBox;3630 ;1650 ;1700 ;440  ;CaptionML=ENU=CY;
                                                 SourceExpr=MyDates[3] }
    { 1000000007;Label  ;220  ;1650 ;3300 ;440  ;ParentControl=1000000006 }
    { 1000000008;TextBox;3630 ;2310 ;1700 ;440  ;CaptionML=ENU=-CM;
                                                 SourceExpr=MyDates[4] }
    { 1000000009;Label  ;220  ;2310 ;3300 ;440  ;ParentControl=1000000008 }
    { 1000000010;TextBox;3630 ;2860 ;1700 ;440  ;CaptionML=ENU=CM;
                                                 SourceExpr=MyDates[5] }
    { 1000000011;Label  ;220  ;2860 ;3300 ;440  ;ParentControl=1000000010 }
    { 1000000020;TextBox;3630 ;4070 ;1700 ;440  ;CaptionML=ENU=CW;
                                                 SourceExpr=MyDates[7] }
    { 1000000021;Label  ;220  ;4070 ;3300 ;440  ;ParentControl=1000000020 }
    { 1000000024;TextBox;3630 ;3520 ;1700 ;440  ;CaptionML=ENU=-CW;
                                                 SourceExpr=MyDates[6] }
    { 1000000025;Label  ;220  ;3520 ;3300 ;440  ;ParentControl=1000000024 }
  }
  CODE
  {
    VAR
      MyDates@1000000000 : ARRAY [10] OF Date;

    PROCEDURE Calc@1000000000();
    BEGIN
      MyDates[2] := CALCDATE('-CY',MyDates[1]);
      MyDates[3] := CALCDATE('CY',MyDates[1]);
      MyDates[4] := CALCDATE('-CM',MyDates[1]);
      MyDates[5] := CALCDATE('CM',MyDates[1]);
      MyDates[6] := CALCDATE('-CW',MyDates[1]);
      MyDates[7] := CALCDATE('CW',MyDates[1]);
    END;

    BEGIN
    END.
  }
}

[/DDET]

To read the (very) technical explanation, here’s the MSDN documentation on DateFormula.