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)
Solved! Go to Solution.
Update 2.0:
There was a bug in the regex that thew an error in Internet Explorer. I've fixed it now, and updated their references below.
Update:
You're definitely going to need to confirm the calculations, but, I think I have some pretty good progress here.
This example breaks everything out into separate steps. Once you've confirmed the calculations, we can work to combine everything into one.
Now, I realize you have date picker fields and not a text field, but for testing, this worked really well.
Additionally, I added in the hours calculations because you mentioned the hourly rate, but I wasn't sure if you actually needed that.
So, the left column of Calculated Value controls finds the full months, days, and hours from the number in the text field.
Hours:
The simplest way to calculate the hours is to multiply the days value from the text field by 24, and then find the remainder after dividing by 24 by using the % symbol. (i.e. The remainder of 51.25 multiplied and then divided by 24 is .25, meaning one fourth of 24, which is 8.)
Calculation:
round((days*24)%24)
Days:
Take the days value from the text field and find the remainder after dividing by 30. (i.e. 51 % 30 = 21 because 30 goes into 51 once leaving 21 remaining) Then look for a decimal point and remove it and everything after it. (i.e. 21.25 days should show 21 days)
Calculation:
replace(days%30,"\\..*",'')
Months:
Take the days value from the text field and divide it by 30. Then look for a decimal point and remove it and everything after it. (i.e. 1.7 months should show 1 month)
Calculation:
replace(days/30,"\\..*",'')
The middle column calculates the time that is charged based off what you mentioned above:
Hours:
If the hours are 8 or greater, show 0. Otherwise, show the hours.
Calculation:
Days:
If the days are greater than 21 OR (hours are 8 or more AND remaining days plus one is greater than 21), show 0. Otherwise, if hours are less than 8, show the remaining days. If they're greater than 8, show remaining days plus 1.
Calculation:
If(or(remainingDays>21,and(hours>=8,remainingDays+1>21)),0,If(hours<8,remainingDays,remainingDays+1))
Months:
If remaining days is greater than 21 OR (hours is greater than or equal to 8 AND remaining days plus one is greater than 21), show months plus one. Otherwise, just show months.
Calculation:
If(or(remainingDays>21,and(hours>=8,remainingDays+1>21)),months+1,months)
---------------------------------------
Use a combination of divide (/) and remainder (%).
In my example, I placed a text field on the form with a name of 'days'.
Then I added a Calculated Value field next to it:
Here is the formula in the Calculated Value field:
*(regex is wrong in this image - should be "\\..*")
If you just use the divide operator, you'll be left with a decimal remainder, which you don't want. Instead, I use the replace() function with a little regex to remove the decimal and everything after it (because rounding won't work for this situation), and then later in the calculation I use the remainder operator to find the remaining days after the division.
This should help get you started in the calculation. If I have more time later, I'll see if I can make anymore progress on it.
Wow, what a genius 🙂
Luckily there are people out there that know maths better than me lol
Just to confirm the costings cos your result look a bit different:
The item costs annually: £4,856.31
which is then divided by 253 (working days) = 404.69 per month
then the 404.69 is divided by 253 (working days) to get the £19.19 per day
then the 19.19 is divided by the 8 (working hours) to get the hourly rate = 2.40
- if more than 8 hrs within a day then charge the daily charge
- if less than 8 hrs within a day then charge the hourly rate
- if more 21 days (but within a 30 days worth of a month) then charge the monthly rate
- *if more than a month then charge the MonthlyCost + the remaining days + Hours
* this means that if for example its booked for 50days and 12 hrs then it is 1 month worth of charge and then plus the remaining days + hours == 1Month+(50 - 30)+Hours
Now what if 65days and 16hrs?
Well, in this case it is 2 x MonthlyCost + Remaining Days + Hours
Hope it makes sense ...
Rather than just saying '2 x MonthlyCost + Remaining Days + Hours', please walk through the calcuations with actual numbers so we can proof the math.
Here's what I get in your example of 50 days and 12 hours:
Breaking down the calculation:
Please double check these numbers with whomever is doing your actual charging to confirm the math is correct.
And your example of 65 days and 16 hours:
Breaking down the calculation:
Please double check these numbers with whomever is doing your actual charging to confirm the math is correct.
I did update the calculations for the monthly, daily, and hourly charges based off your last post. That should make the numbers more accurate.
I published my form as a Nintex Live Form so, feel free to test it out yourself:
Now, once you've confirmed the math, there are still additional steps to go through.
First, my example is just a text field where you enter the days. You mentioned that you have date pickers. That means you'll have to actually run a calculation (probably datediff) to get the number of days and hours from those date pickers. If your hours are not in the format of being portion of one day (i.e. decimal), then the other calculations will need to be updated as well.
Secondly, you probably don't want your form to have all the calculated value fields and labels like mind does so, you'll need to get those calculations combined down into just a few fields.
To calculate the montly, daily, and hourly charges, I used Form Variables. That may be a big help for you with the other calculations as well.
There is an error when testing your LiveForm.
Very interesting. I do the majority of my work in Chrome, and it works great in that browser. However, I did just try it in IE, and you're right, there's an error.
I'll look into that, but in the end, we still need you to confirm the calculations so, please test in Chrome for now.
I found the bug and it's been fixed. The form should be able to be used in Chrome or IE now. Thanks for catching that.
the preview works 🙂
Please leave the form link live so I can use it for demo any time.
The only thing I need to figure out is how to fit the total days/hours as you currently type in manually. Whereas in my case there is [FromDate + Time] and [UntilDate + Time] date fields which then shows how any days (diffDates) and how many hours (diffHour) and how many minutes.