Convert DateFormula to text

ta5ta5 SwitzerlandMember Posts: 1,164
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

  • ta5ta5 SwitzerlandMember Posts: 1,164
    Hi AK
    Sorry for late answer. I'll try that, thank you for helping.
    Thomas
  • ta5ta5 SwitzerlandMember 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
  • vaprogvaprog Member Posts: 960
    Well, and how misleading is that result if the date formula was '<+CM>', for instance?
  • ta5ta5 SwitzerlandMember Posts: 1,164
    Hi Vaprog
    Thank you for raising this topic.
    Good point indeed, but for my special requirement ok.
    Thomas
  • danielonsombidanielonsombi 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 ');
Sign In or Register to comment.