Discussions

Expand all | Collapse all

Minimum Due Date Based on End of Quarter Date

  • 1.  Minimum Due Date Based on End of Quarter Date

    Posted 25 days ago
    Hello!

    I'm looking to create a date field formula that returns a due date that is 10 business days after a creation date field (this is auto populated based upon the recorded added date) or a static end date (the end of our fiscal quarter). I would also like this calculated date to be made static if the record has been already review by checking a checkbox. An example is below.

    Scenario 1:
    Quarter End Date: 7/31/20
    Date Created: 6/18/20
    Returned Due Date: 7/2/20 (+ 10 business days from Date Created)

    Scenario 2: 
    Quarter End Date: 7/31/20
    Date Created: 7/23/20
    Returned Due Date: 7/31/20 (<10 business days from Date Created but not further than quarter end date)

    I know I'll need to use a min function but I'm unsure how to set up the formula. I'm guessing I'll have to add a calculated due date field that calculates the above and then populates the due date field with the due date if the checkbox is not checked off. Any help would be appreciated. Thanks!


    ------------------------------
    Josh Baker
    ------------------------------


  • 2.  RE: Minimum Due Date Based on End of Quarter Date

    Posted 25 days ago
    you could make two variables then use an if function at the end to determine whether to use it.

    something like:
    var date CalcDate = [Date Created] + Days(10);
    //^Calculates your Returned Due Date

    var date CheckDate = (If($CalcDate > [Quarter End Date], [Quarter End Date], $CalcDate));
    //^Checks to make sure CalcDate is on or before quarter end date

    If([Checkbox]=true,null,$CheckDate)
    //^if your checkbox is checked then it doesn't return anything, but if it isn't checked, it returns your returned due date or visa versa if its supposed to be the other way around

    ------------------------------
    Jordan McAlister
    ------------------------------



  • 3.  RE: Minimum Due Date Based on End of Quarter Date

    Posted 20 days ago
    Hey Jordan,

    Thank you for the below that all makes sense to me. The last item I wanted to see if there is a solution for is below.

    We've made it past the quarter end date of 7/31/20. Once we update the quarter end date in the formula to a new date in the future (say 10/31/20) wouldn't this update all the prior records that had a due date populated and were then subsequently checked off as complete to a Null value?

    I would like the system to populate the due date once and not update the date in the field once it's been checked off as complete. Hope that makes sense, if not I can provide a more detailed example. 

    Thanks for all your help!

    ------------------------------
    Josh Baker
    ------------------------------



  • 4.  RE: Minimum Due Date Based on End of Quarter Date

    Posted 19 days ago
    I think I get what you're saying - how do you guys update your quarter end date? is there a formula field that auto generates that date?

    ------------------------------
    Jordan McAlister
    ------------------------------



  • 5.  RE: Minimum Due Date Based on End of Quarter Date

    Posted 19 days ago
    The plan was to just populate a date in the [Quarter End Date] spot you outlined below and manually update that four times a year in this one date formula field. Will this work?

    Also, in terms of setting up the variables this is something I haven't done before. Do I make the variables directly in the date formula field or is this something I'd have to do separately outside of that field and then reference? If this isn't an easy answer, if you could point me to any articles discussing it in the support pages that would be appreciated.

    Again, thank you for your help!

    ------------------------------
    Josh Baker
    ------------------------------



  • 6.  RE: Minimum Due Date Based on End of Quarter Date

    Posted 19 days ago
    Edited by Jordan McAlister 19 days ago
    You can probably make [Quarter End Date] a field of its own maybe with:
    If(
    Today()<(ToDate("3/31/" & (Year(Today())))),ToDate("3/31/" & Year(Today())),
    Today()<(ToDate("6/30/" & (Year(Today())))),ToDate("6/30/" & Year(Today())),
    Today()<(ToDate("9/30/" & (Year(Today())))),ToDate("9/30/" & Year(Today())),
    Today()<(ToDate("12/31/" & (Year(Today())))),ToDate("12/31/" & Year(Today()))
    )

    This also depends on if your quarter end dates are the same each year, but I noticed you used 7/31 as a quarter end date. i don't know if that was just for an example or if you use different days for that. There also might be an easier way to write that code above but that's what came to mind first.

    ------------------------------
    Jordan McAlister
    ------------------------------