Convert DateFormula to text

ta5

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

AK

AFAIK there is no trick to do that, you have to convert that yourself. On the bright side, this should be easy.

Remco_Reinking

Well, there should be a small trick to do it. Use the dateformula on a date field. Substract the outcome from the original date and save it in a duration variable. That one will show as 3 months duration. Did not test it, but give it a try.

AK

AFAIK there is no trick to do that, you have to convert that yourself. On the bright side, this should be easy.

ta5

Hi AK
Sorry for late answer. I'll try that, thank you for helping.
Thomas

Remco_Reinking

Well, there should be a small trick to do it. Use the dateformula on a date field. Substract the outcome from the original date and save it in a duration variable. That one will show as 3 months duration. Did not test it, but give it a try.

ta5

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

Well, and how misleading is that result if the date formula was '<+CM>', for instance?

ta5

Hi Vaprog
Thank you for raising this topic.
Good point indeed, but for my special requirement ok.
Thomas

danielonsombi

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 ');