Hello guys,

I am trying to get the equivalent of the Excel function YEARFRAC in WebFOCUS. YEARFRAC expresses the difference between two dates as a year fraction. I was trying to write code using HDIFF that I could use to math out the equivalent, but it isn't working for me.

So for example if I've got a start date of 08/26/2010 and an end date of 05/19/2012 and I use HDIFF to get the year difference

 
-*ENDDATE = 2012-05-19 00:00:00
-*STARTDATE = 2010-08-26 00:00:00

YEARS_BETWEEN/D12.0 = HDIFF(ENDDATE,STARTDATE,'YEAR','D12.2');

 

YEARS_BETWEEN will return a value of 2.0 (I'm guessing it is rounding up from 1.73 which is what YEARFRAC returns). How do I get it to express the value without rounding?

This message has been edited. Last edited by: rray9895,

Try YEARS_BETWEEN/D12.2

Changed the decimal point placement to D12.2, but it is still giving me the rounded number.

It just now shows 2.00 instead of just 2.

I would go with this instead

DEFINE FILE CAR
STARTDATE     /YYMD  = '20100826';
ENDDATE       /YYMD  = '20120519';
MM_BETWEEN    /D12.2 = DTDIFF(ENDDATE, STARTDATE, MONTH);
DD_BETWEEN    /D12.2 = DTDIFF(ENDDATE, STARTDATE, DAY);
END
TABLE FILE CAR
SUM STARTDATE
    ENDDATE
    MM_BETWEEN
    DD_BETWEEN
    COMPUTE YYMM_FRACTION /D6.3 = MM_BETWEEN / 12;
    COMPUTE YYDD_FRACTION /D6.3 = DD_BETWEEN / 365;
BY MODEL NOPRINT
WHERE READLIMIT   EQ 1;
WHERE RECORDLIMIT EQ 1;
END

That is giving you the number of month or days between the dates. You can then perform the maths to have it as a fraction of year.

I also tried this..


DEFINE FILE CAR
ENDDATE/YYMD = '20120519';
STARTDATE/YYMD = '20100826';
YEARS_BETWEEN/D12.2 = DTDIFF(ENDDATE,STARTDATE,YEAR);
END
TABLE FILE CAR
PRINT YEARS_BETWEEN
BY COUNTRY
END


And it gave back 2.00 as well, I think it doesn't look at the full date just the YEAR portion provided when doing the calculation

quote:

Originally posted by MartinY:
I would go with this instead

DEFINE FILE CAR
STARTDATE     /YYMD  = '20100826';
ENDDATE       /YYMD  = '20120519';
MM_BETWEEN    /D12.2 = DTDIFF(ENDDATE, STARTDATE, MONTH);
DD_BETWEEN    /D12.2 = DTDIFF(ENDDATE, STARTDATE, DAY);
END
TABLE FILE CAR
SUM STARTDATE
    ENDDATE
    MM_BETWEEN
    DD_BETWEEN
    COMPUTE YYMM_FRACTION /D6.3 = MM_BETWEEN / 12;
    COMPUTE YYDD_FRACTION /D6.3 = DD_BETWEEN / 365;
BY MODEL NOPRINT
WHERE READLIMIT   EQ 1;
WHERE RECORDLIMIT EQ 1;
END

That is giving you the number of month or days between the dates. You can then perform the maths to have it as a fraction of year.

Got it! I have to use the HDIFF for DAYS rather than YEAR and divide by 365 to return the YEARFRAC equivalent. Thank you so much!

Will that return the correct value for you on 31st of December of a leap year?
That would be ~1.0027, which is >1.

I've had a NFR sitting out there since 2011 asking IBI to address this issue. In my problem report that became the NFR, IBI said that DATEDIF truncates while HDIFF rounds. The NFR status says it is in "Programming" whatever that means. Hard to believe it would be in programming over 8 years. When I'm using date time fields I generally use Minutes as the unit. Then if I need fractional days, I divide by 1440 minutes per day. If I need fractional years, I divide by 525600 minutes per year or 527040 minutes per year the from and to dates cross a leap day.

This message has been edited. Last edited by: jgelona,