Forum Discussion

HansHamm's avatar
HansHamm
Qrew Assistant Captain
7 years ago

Calculating Per Diems Across Multiple Weeks

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??
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Can you clarify?  Are you writing a report with an <ask the user> question, or is this a formula,  or somehow both.
  • HansHamm's avatar
    HansHamm
    Qrew Assistant Captain
    7 years ago
    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...
  • HansHamm's avatar
    HansHamm
    Qrew Assistant Captain
    7 years ago
    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!