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

# Problem calculating a conditional date

### 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

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

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

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

• ###### 4. Re: Problem calculating a conditional date

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