4 Replies Latest reply on Oct 25, 2014 10:38 AM by AlejandraGarcia

    Problem calculating a conditional date

    AlejandraGarcia

      Title

      Problem calculating a conditional date

      Post

      I would like to calculate debt payments by fortnights. If someone pays between the 1st and 6th day of a month, the next payment would have to be on the 16th; If someone pays between the 14th and the 20th of a month, the next payment would have to be on the 1st of next month:

      Example

      Last payment field (no problem with this field): Oct 19, 2014

      Next payment field (I can't calculate this field): Nov 1, 2014

      This is the calculation I'm using for Next payment field, but it always shows day 16, so the conditional is not working:

      If ( Day ( Date | Last Payment  ≥ 1 and Day ( Date | Last Payment ≤ 6 ))  ; Date ( Month ( Date | Last Payment) ; Day ( 16 ) ; Year ( Date | Last Payment ) ) ; If ( Day ( Date | Last Payment  ≥ 14  and Day ( Date | Last Payment  ≤ 20 )) ; Date ( Month ( Date | Last Payment + 1) ; Day ( 1 ) ; Year ( Date | Last Payment ))))

      Thanks

        • 1. Re: Problem calculating a conditional date
          philmodjunk

          You have  )'s in the wrong place

          If ( Day ( Date | Last Payment ) ≥ 1 and Day ( Date | Last Payment ) ≤ 6   ;

          • 2. Re: Problem calculating a conditional date
            raybaudi

            This is your calculation in a more readable format:

            Let(
            date = Date | Last Payment ;
            Case(
            Day ( date ) ≥ 1 and Day ( date ) ≤ 6 ; Date ( Month ( date ) ; 16 ; Year ( date ) ) ;
            Day ( date ) ≥ 14 and Day ( date ) ≤ 20 ; Date ( Month ( date ) + 1 ; 1 ; Year ( date ) )
            )
            )

            BTW: what has to happen if someone enters the 7 ( till 13 ) or the 21 ( till 31 ) of a month ?
            In that case is better:

            Let(
            date = Date | Last Payment ;
            Case(
            Day ( date ) < 14 ; Date ( Month ( date ) ; 16 ; Year ( date ) ) ;
            Date ( Month ( date ) + 1 ; 1 ; Year ( date ) )
            )
            )

            • 3. Re: Problem calculating a conditional date
              AlejandraGarcia

              Thank you very much!!!! it works perfectly :)

              • 4. Re: Problem calculating a conditional date
                AlejandraGarcia

                I loved that Case function... I'm trying to make a conditional with that function, but is not working:

                I'm working on a loan's database, and I would like to have a function in which when the payment has not been done by the agreed date, then a field with "payment not done" is shown:

                Payment field                  Payment date agreed                  Current date                  Late Payment

                (Fechas)              (Fecha  | Primer pago acuerdo)               (Fecha actual)                 "Primer pago atrasado"

                Empty                                    Oct 10 2014                                Oct 25, 2014                   "Payment not done"

                I have this function but is not working:

                Case ( 

                IsEmpty ( Pagos::Fechas ) and Datos generales::Fecha | Actual   >   Datos generales::Fecha | Primer pago acuerdo ; "Primer pago atrasado" ;

                IsEmpty ( Pagos::Fechas ) and Datos generales::Fecha | Actual    <    Datos generales::Fecha | Primer pago acuerdo ; "no atrasado" ;

                IsValid (Pagos::Fechas ) ; "no atrasado"
                 )

                What happens is that if the payment field is empty regardless dates, it shows "Payment not done"

                PS. The payment field is from portal row