Convert DateFormula to text

ta5
ta5 Member Posts: 1,164
in NAV Three Tier
Hi experts
Is it possible to convert a DateFormula to text?
Example: Input=3M, Output=3 Months

Detail: FORMAT works, but returns just 3M as a text string.

Documentation for FORMAT does not mention DATEFORMULA...

Thanks in advance.
Thomas

Best Answers

Answers

  • ta5
    ta5 Member Posts: 1,164
    Hi AK
    Sorry for late answer. I'll try that, thank you for helping.
    Thomas
  • ta5
    ta5 Member Posts: 1,164
    Hi Remco

    Awsome idea, this works very nice! 
    EVALUATE(myDateFormula,'<6D>');

myDate := TODAY;
myDateTime := CREATEDATETIME(myDate,0T);

myDate2 := CALCDATE(myDateFormula,myDate);
myDateTime2 := CREATEDATETIME(myDate2,0T);

myDuration := myDateTime2 - myDateTime;

MESSAGE('Result %1',myDuration);

    Thanks a lot, thanks for contributing!

    Thomas
  • vaprog
    vaprog Member Posts: 1,162
    Well, and how misleading is that result if the date formula was '<+CM>', for instance?
  • ta5
    ta5 Member Posts: 1,164
    Hi Vaprog
    Thank you for raising this topic.
    Good point indeed, but for my special requirement ok.
    Thomas
  • danielonsombi
    danielonsombi Member Posts: 1
    Incase one has such an issue, This is how I implemented it in BC Version 14:


    Created a Date formula and dateformula description fields in my table.

    On the Date formula field onvalidate trigger, Called the FnGetDateFormulaDescription which receives the keyed in Formula as parameter.

    Below are the codeunit functions:


    FnGetDateFormulaDescription(DF : DateFormula) : Text

    var ODF DateFormula
    var Projected Date
    var NoOfDays Integer
    var Period Text
    var NewString Text
    var CurrDf DateFormula
    var Pos Integer

    IF DF = ODF THEN
    EXIT;

    CurrDf := ODF;

    Period := '';
    NewString := FORMAT(DF);
    Length := STRLEN(NewString);
    Pos := STRPOS(NewString, '+');

    WHILE Pos <> 0 DO
    BEGIN
    EVALUATE(CurrDf, COPYSTR(NewString, 1, Pos-1));
    Period += FnGetFormulaDescription(CurrDf);

    NewString := COPYSTR(NewString, Pos + 1);
    Pos := STRPOS(NewString, '+');
    END;

    EVALUATE(CurrDf, COPYSTR(NewString, 1));
    Period += FnGetFormulaDescription(CurrDf);

    EXIT(Period);



    LOCAL FnGetFormulaDescription(DF : DateFormula) : Text

    var DFStr Text

    DFStr := FORMAT(DF);

    IF STRPOS(DFStr, 'D') <> 0 THEN
    EXIT(COPYSTR(DFStr,1,1) + ' Days ');

    IF STRPOS(DFStr, 'M') <> 0 THEN
    EXIT(COPYSTR(DFStr,1,1) + ' Months ');

    IF STRPOS(DFStr, 'Q') <> 0 THEN
    EXIT(COPYSTR(DFStr,1,1) + ' Quarters ');

    IF STRPOS(DFStr, 'Y') <> 0 THEN
    EXIT(COPYSTR(DFStr,1,1) + ' Years ');

    IF STRPOS(DFStr, 'CM') <> 0 THEN
    EXIT(COPYSTR(DFStr,1,1) + ' Current Month ');

Categories