# Discussions

View Only

## Calculating Per Diems Across Multiple Weeks

• #### 1.  Calculating Per Diems Across Multiple Weeks

Posted 04-23-2018 15:55
I am really stumped on this one and need some help.
The user will select the Hotel In - is on or after (this is actually the Hotel Check-Out Date to give me one part of the range). Then they will select the Hotel Out - is on or before (the Hotel Check-In Date)...
If an associate stays out 1 week or less no problem as this calculation works:
If(ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])<=7,ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])*25)

Where the problem occurs is when an associate is out for more than one week. We pay per diems weekly. So, for example week one could be \$175.00 and the next week \$125.00.
The issue I am having is how to write the formula so it will pay out \$175.00 the first week and \$125.00 (or less if it only goes, say 2 nights into the next week)

Any ideas out there??

• #### 2.  RE: Calculating Per Diems Across Multiple Weeks

Posted 04-23-2018 16:02
Can you clarify?  Are you writing a report with an <ask the user> question, or is this a formula,  or somehow both.

• #### 3.  RE: Calculating Per Diems Across Multiple Weeks

Posted 04-23-2018 16:09
It is the formula that I am having the issue with, thought maybe the "<ask the user>" might help to understand the start date and end date...

• #### 4.  RE: Calculating Per Diems Across Multiple Weeks

Posted 04-23-2018 16:13
try this

var number HotelDays =
ToDays([Hotel Check-Out Date]-[Hotel Check-In Date]);

IF(
\$HotelDays <=7, \$HotelDays *175,
\$HotelDays *125)

The formula uses a formula variable to avoid repetition.

https://help.quickbase.com/user-assistance/formula_variables.html

• #### 5.  RE: Calculating Per Diems Across Multiple Weeks

Posted 04-26-2018 14:07
Hey Mark... this didn't work for me, but thinking about it I came up with the following:
Added a field in the table to provide the date of the second Monday in the record
If([Number of Nights Out]>7,
If(Today()>[Hotel Check-In Date],LastDayOfWeek(Today())+Days(2),
If([Hotel Check-In Date]>=Today(),[Hotel Check-In Date])))

Then added this formula in the report to reference the aforementioned field:
If((If([Number of Nights Out]<=7,[Number of Nights Out],
If([Per Diem Date]=[Hotel Check-In Date],7,ToDays([Hotel Check-Out Date]-[Per Diem Date])))*25)>=175,175,
If([Number of Nights Out]<=7,[Number of Nights Out],
If([Per Diem Date]=[Hotel Check-In Date],7,ToDays([Hotel Check-Out Date]-[Per Diem Date])))*25)

So far it seems to work... I appreciate your help as it did start me thinking in a different direction!