Discussions

Expand all | Collapse all

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!