2 Replies Latest reply on Apr 13, 2012 7:57 AM by Kevin Taylor

    How can I use Date and DateAdd in a single calculation to tranform a text field to date and to convert to our fiscal calendar?

    Kevin Taylor

      I've been able to convert our Fiscal Period ID text field to a date and then create a 2nd calculation using DateAdd to subtract 5 months in order to correctly reflect the actual calendar date.  Formulas provided Below and sample workbook attached.

       

      Looking for a solution that utilizes a single calculation.

       

      FiscalDate =

       

      DATE(

      RIGHT([FiscalPeriodID],2) +

      "/15/" +

      LEFT([FiscalPeriodID],4)

      )

       

      **************

      Actual Date =

      DATEADD('month', -5,[FiscalDate] )

       

      Cheers,

      Kevin Taylor