Hello
I have created a request to loan an item which in return will show the [calculated value] to be paid by the user. However, the calculation varies depends on the pength of period for how much it was loaned for which should be pretty straight forward and easy, right?
Well... not so easy for me at least :)
So, let me explain what the current situation is:
There are date fields: [FromDate] and [UntilDate]
There is also a Formula field on the form which contains the calculation expression.
The criteria is:
If the DateDifference between [FromDate] and [UntilDate] is above 21 days and within a month then show the MonthlyCost (which is a lookup field) otherwise calculate UsedHours * HourlyRate
However the downfall is; what if the user loands it for 35 days, or 45 days or 60 days ...
I am now confused to how to get to a conclusion where we say to charge the monthly rate + the remaining days ... for example:
YearlyRate = £4,856.31
MonthlyRate = £404.69
DailyRate = £19.20 (capt to 8hrs per day)
HourelyRate = £2.40 (this is used if the loan is belw 8 hrs within one day)
If a user loans it for 45 days, what is the total cost to be charged?
If we say, well 45 days - 21 days = 24days remaining
which makes 1 x MonthlyCost + (24 days x DailyRate) = 404.69 + (24 days x 19.20)
// by this am I not overcharging the user, right?
or is it rather 45 days - 30 days (because we say if above 21 days then charge for whole month) = 15 days remaining >> 1 x MonthlyCost + (15 days x DailyRate) = 404.69 + (15 x 19.20) right?
So, what about when loaning it for 60 days?
Well, then the user needs to pay for 2 x MonthlyCharge
... on and on and on ....
I need this to put it in a calculation utilising the Form Variables as well.
I have the variable to get charges if anything is over 21 days but the rest is difficult for me.
IF(dateDiffDays(FromDate,UntilDate) > 21, lookup("ListName","ItemName", parseLookup(Item), "MonthlyCost"), calculateDailyCost+calculateHourlyRate)