Get precise end date base on type of rent

[williamsanj13]

Hello there

i just create a rent app,,

how to find the end date (date menu 2) based on Durasi sewa (rent duration), and Jenis Sewa (Rent Type)

Harian = day | Bulanan = Month | Tahunan = Year

Before this, i usually used this expressions :
 Start date +  30 days for Bulanan, and 360 days for tahunan.
actually, this enough for  now, but i want the end date  more specific because every month has different days

thanks

[Suvrutt_Gurjar · Accepted solution]

Please try something like 

SWITCH( [Jenis Sewa], 

                "Bulanan",

                 EOMONTH( [Tanggal Mulai] , [Durasi Sewa] -1) + DAY([Tanggal Mulai]),

                  "Tahunan",

                   EOMONTH( [Tanggal Mulai] , ([Durasi Sewa]*12) -1) + DAY([Tanggal Mulai]),

                  "Harian",

                   [Tanggal Mulai] +[Durasi Sewa],

                    [Tanggal Mulai]

                   )

Please test well.

[williamsanj13]

it work's well

thank you

but i still not understand, why you put -1 on the expressions?

[Suvrutt_Gurjar]

You are welcome. Good to know it works well. I put -1 in the expression because the combination of EOMONTH(  [Tanggal Mulai],0) + DAY([Tanggal Mulai]) always adds one month already. 

This is so because EOMONTH(  [Tanggal Mulai],0) calculaes end of the current month and DAY([Tanggal Mulai]) adds required days of the next month , so that addition totals to 1 month.

So if your duration([Durasi Sewa]) is 3 months, one month is already added by the combination of    EOMONTH(  [Tanggal Mulai],0) + DAY([Tanggal Mulai]) . So you need to subtract 1 from [Durasi Sewa] to add remaining 2 months.

Hope I could explain.

Edit: Also please be aware that the expression will have some minor errors on edge cases due to nonstandard number of days in months. Certain months have 31 days ( Jan, Mar, May,  July, Aug, Oct and Dec), certain months  30 days ( Apr, Jun, Sep , Nov)  and February has 28 days or 29 days ( leap year). So if your [Tanggal Mulai] is a date of 31 and the duration takes it to a month of 30 days , it will go to the 1st day of the next month as there is no date of 31 in the target month.

In case of Feb month, the dates may go to 1st or 2nd or even 3 rd of March, depending if the [Tanggal Mulai] is on 29th , 30th or 31st of a month because there are no 29, 30 and 31 dates in Feb in non leap year and 30 and 31 in Feb leap year.

The errors could be possibly fixed based on how and if you want to to handle those edge cases but the expression will become that much more complex with more testing required.