3 Replies Latest reply on Jan 2, 2015 3:20 PM by philmodjunk

    Date Range

    BrooksLeisure

      Title

      Date Range

      Post

      Hi,

      I am trying to make a calculation field that auto enters a value based on the specified date range.

      For example if the date is between 01/12/13 and 30/11/14 I need it to enter 13/14

      between 01/12/14 and 30/11/15 I need it to enter 14/15

      between 01/12/15 and 30/11/16 I need it to enter 15/16 etc.

      I am using FM12. Many thanks for any help in advance 

        • 1. Re: Date Range
          jbante

          Let ( [
              _year1 = Year ( Table::date1 ) ;
              _year2 = Year ( Table::date2 )
          ] ;
              Right ( _year1 ; 2 )
              & If ( _year2 > _year1 ; "/" & Right ( _year2 ; 2 ) )
          )

          • 2. Re: Date Range
            BrooksLeisure

            Thank you for your help. However this is the calculation that works for me;

            Case(

            Date(12;1;2013) ≤  Date
            and
            Date(11;30;2014) ≥ Date ; 1314
            ;
            Date(12;1;2014) ≤  Date
            and
            Date(11;30;2015) ≥  Date ; 1415
            ;
            Date(12;1;2015) ≤  Date
            and
            Date(11;30;2016) ≥  Date ; 1516
            )

            • 3. Re: Date Range
              philmodjunk

              Yet eventually, you'll have to change your dates. Jeremy had the right idea but the calculation needs to check month values as part of the calc. 

              Let ( [ mnth = month ( table::datefield );

                         Yr = year ( table::datefield )

                        ];

                          If ( mnth= 12 ; yr  & "/" & yr + 1 ; yr - 1 "/" & yr )

                       ) // let