Forum Discussion

JoelN's avatar
JoelN
Helper I
4 years ago
Solved

Paginated Report - YEARFRAC similar function

Hey Guys!

Im trying to accomplish the same fuction of YEARFRAC like excel has but in my paginated report. I tried DATEDIFF expression and got close by not the correct answer. Any help is much appreciated.

Goal: (YEARFRAC Date1, Date2) Ex. YEARFRAC("8/28/2020","12/31/2021")

Answer: 1.34



Attempt:
=CStr(DATEDIFF("m",Fields!Acquisition_Close_Date.Value,First(Fields!MaxReturn.Value, "MaxReturnDate"))\12)&"."& CStr(DATEDIFF("m",Fields!Acquisition_Close_Date.Value,First(Fields!MaxReturn.Value, "MaxReturnDate")) Mod 12)
Attempt Answer: 1.4 

Thanks for your help!

 

Paginated Report - YEARFRAC or DATEDIFF Help
  • FarhanAhmed's avatar
    FarhanAhmed
    4 years ago

    Try 

    = DATEDIFF("m",Fields!Acquisition_Close_Date.Value,First(Fields!MaxReturn.Value, "MaxReturnDate")) / 365
  • JoelN's avatar
    JoelN
    4 years ago

    FarhanAhmed the solution works! I had to adjust the 365 to 12. 
    Do you know how to two decimals instead on showing just one?
    Thanks!

2 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    4 years ago

    Try 

    = DATEDIFF("m",Fields!Acquisition_Close_Date.Value,First(Fields!MaxReturn.Value, "MaxReturnDate")) / 365
    • JoelN's avatar
      JoelN
      Helper I
      4 years ago

      FarhanAhmed the solution works! I had to adjust the 365 to 12. 
      Do you know how to two decimals instead on showing just one?
      Thanks!