Calculating Per Diems Across Multiple Weeks

  • 1
  • 1
  • Question
  • Updated 7 months ago
  • Answered
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??
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb

Posted 7 months ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Can you clarify?  Are you writing a report with an <ask the user> question, or is this a formula,  or somehow both.
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
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...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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


 
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
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!