Help with a complex calculation based on a few factors...

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:

If(hours<8,hours,0)

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.

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:

• 50.5 days is 50 days and 12 hours
• 50 days contains 1 full month (30 days) with a remainder of 20 days
• 12 hours is >= to 8 hours, meaning it should be converted to one full day
• This means we have 1 full month and 21 days to charge
  • Since the requirements from your original post state that the days should only be converted to another full month when they're greater than 21 days, this does not meet that criteria
• So, we take the 1 full month x the monthly rate of 404.69 and add it to the 21 days x the daily rate of 19.19
• This gives us (1 x 404.69) + (21 x 19.19) -> 404.69 + 402.99 -> 807.68

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:

• 65.66 days is 65 days and 16 hours
• 65 days contains 2 full months (30 days x 2) with a remainder of 5 days
• 16 hours is >= to 8 hours, meaning it should be converted to one full day
• This means we have 2 full months and 6 days to charge
• We take the 2 full months x the monthly rate of 404.69 and add it to the 6 days x the daily rate of 19.19
• This gives us (2 x 404.69) + (6 x 19.19) -> 809.38 + 115.14 -> 924.52

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:

https://ntx.lv/2BT0fHq

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.

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.

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.