You are viewing limited content. For full access, please sign in.

Question

Question

Javascript code to multiply per diem rate based on the dates selected

asked on September 27, 2016 Show version history

Hello,

What Javascript code could I use to be able to calculate the per diem rate based on the depart/return dates?

 

For example, if the per diem rate is 60, and they select a depart date of 9/27/2016 and a return date of 9/28/2016, it should multiply it by 2 (# of days). So the per diem rate will be 120. 

 

Thanks!

0 0

Replies

replied on September 27, 2016

Hi Jorge,

On the latest version of Forms, you actually don't need to use JavaScript; you can apply this calculation:

=DATEDIF(Depart_Date,Return_Date,"D") * Per_Diem

The DATEDIF function takes three arguments, in order:

  1. Start date of the time period
  2. End date of the time period
  3. Unit to calculate; in this case "D" for days.

You can read more from the OpenFormula specifications for DATEDIF.

Note that in the above, I've assumed names for the field variables and that the three fields are separate; if they are included in table, e.g. you would have to modify the variable references accordingly.

Hope this helps!

1 0
replied on September 28, 2016

I substituted the formula with their corresponding variable names and I get this error "Calculation contains a circular reference". 

0 0
replied on September 28, 2016

Are you putting this in the input field for the Per_Diem_1 variable? If so, I think I misunderstood the intent from the original post. I had thought you would be taking the two date fields and the value of "Per Diem" to populate a completely different field.

0 0
replied on September 28, 2016

I have a lookup set up that when a user enters a zip code, the per diem automatically gets populated with the daily rate. What I am trying to accomplish, is that when they select the depart/return dates, that it will multiply that per diem number that was already populated by the number of days. Is that why I am getting the calculation contains a circular reference? Do I need to create another field that will be just the per diem daily rate and then another one for the total which is where I would place the formula?

 

Thanks!

0 0
replied on September 28, 2016

I added a "Per Diem Daily Rate" field. That one gets populated when inputting the zip code. On the "Per Diem" field, I used this formula "=DATEDIF(Date_Range_,Return_Date_ ,"d") * Per_Diem_Daily_Rate_"

 

 

It gives me that calculation error.

0 0
replied on September 28, 2016

After some troubleshooting, I no longer get any calculation errors. The only thing now is that if I choose the same depart date and return date, it returns a value of 0. I want it to count as 1 day if the depart date and return date are the same. 

 

0 0
replied on September 28, 2016 Show version history

Would you still want "today" and "tomorrow" to be counted as 1 day in between?

If so you could try the formula

=IF(
  Date_Range_ = Return_Date_,
  Per_Diem_Daily_Rate_,
  DATEDIF(Date_Range_,Return_Date_,"d") * Per_Diem_Daily_Rate_
)

which should return the per diem rate (trivially multiplied by 1 day) if the dates are the same, and calculate the proper total otherwise.

EDIT: Updated to correct variable name

0 0
replied on September 28, 2016

Today and tomorrow should count as 2 days. 

I added the formula to Javascript, however, it does not do anything. Notice how the "D" displays in red.

0 0
replied on September 28, 2016

No, that's not JavaScript, you would add it in the Calculation field. However it sounds like that still isn't what you want.

What happens is each date (day, month, year) is mapped to a corresponding integer, similar to a UNIX timestamp. When calculating the DATEDIF value, these numbers are just subtracted to get the result. So if they are the same, n - n = 0. For the "today" and "tomorrow" case, (n + 1) - n = 1. etc. So we can just add 1 to this result to include both ends of the period, then multiply by the per diem rate:

=(DATEDIF(Date_Range_,Return_Date_,"d") + 1) * Per_Diem_Daily_Rate_

Is this better?

1 0
replied on September 28, 2016

That works exactly how I wanted!

 

Thank you !

0 0
replied on September 28, 2016

You're welcome, Jorge.

0 0
replied on October 11, 2016

I have a similar calculation, but I need the first and last day to be 75% of per diem.  Can this be accomplished in a single calculation?

0 0
replied on October 11, 2016

Yes, you could do it by calculating the product of the per diem rate and the intervening DATEDIF - 1 days (since there are DATEDIF + 1 days including the end days, minus 2 for the first and last day), then add those 2 days * (0.75 * per diem) = 1.5 * per diem.

= ((DATEDIF(Date_Range_,Return_Date_,"d") - 1) * Per_Diem_Daily_Rate_) + (2 * (0.75 * Per_Diem_Daily_Rate_))

Factoring out the per diem rate and combining other terms, we can clean this up:

= (DATEDIF(Date_Range_,Return_Date,"d") + 0.5) * Per_Diem_Daily_Rate_
2 0
You are not allowed to follow up in this post.

Sign in to reply to this post.