# Calculating Per Diems Across Multiple Weeks

• 1
• Question
• Updated 1 year ago
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??
• 710 Points

Posted 1 year ago

• 1
• 72,478 Points
Can you clarify?  Are you writing a report with an <ask the user> question, or is this a formula,  or somehow both.
• 710 Points
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...
• 72,478 Points
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

• 710 Points
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!