Expand all | Collapse all

Calculate date after 7 business days

  • 1.  Calculate date after 7 business days

    Posted 09-06-2018 17:30
    Our customers pay us on a set date each month via autopay through our payment processor.  Our payment processor sends the money to us 7 business days later.

    We have a column in Quick Base that records the set date using just the day of month eg. 1 if they pay on the 1st of every month, 10 if they pay on 10th of every month.

    What I want to produce now is two columns, one that tells me for the current month when to expect the deposit from the payment processor, and then one that tells me for the next month.

    The problem is weekends and holidays.  For example this month the 1st was a Saturday (not a business day) and then the Monday was a holiday (not a business day) so day 1 of the 7 was the 4th, so expected deposit date is the 12th.

    Is it possible to automatically show this expected date based on working days?  Current month is the priority, but if another field can do next month as well then that's a bonus.

  • 2.  RE: Calculate date after 7 business days

    Posted 09-06-2018 21:25
    Hi Mike, 

    There are some functions in the formula to work with weekdays, These should help you build the formula for both months. Here is my attempt without testing on data.

    This month:
    WeekdayAdd(ToWeekdayN(todate(totext(month(today()) &"/"& [Payday] &"/"& year(Today())))),7)

    For next month, Add a month to Today, could result in next year.
    WeekdayAdd(ToWeekdayN(todate(totext(1 &"/"& [Payday] &"/"& year(Today())+1))),7),
    WeekdayAdd(ToWeekdayN(todate(totext(month(today())+1 &"/"& [Payday] &"/"& year(Today())))),7)

     (Date d, Number n)

    Description: Returns the date that is n weekdays past the given date d.  n may be negative to move backward in time.

    Example: WeekdayAdd([Start], [Duration]) returns the date that results if you add the value in the Duration field to the date in the Start field and count only weekdays. 

    WeekdayAdd(ToDate("6/20/2003"), 2) returns 6/24/2003

    WeekdayAdd(ToDate("6/24/2003"), -2) returns 6/20/2003

    If you have a date field named "Start Date" and that field has a value of 6/23/2003, then WeekdayAdd([Start Date], -2) returns 6/20/2003

    ToWeekdayN (Date d)

    Description: If the given date d is a weekday returns it, otherwise returns the next occurring weekday. 

    Example: To WeekdayN([Order Date]) returns the date in the Order Date field if it's a weekday. If not, it returns the date of the next weekday.

    ToWeekdayN(ToDate("6/21/2003")) returns 6/23/2003

  • 3.  RE: Calculate date after 7 business days

    Posted 03-05-2020 14:07
    this is great, but it doesn't account for holidays. Any suggestions on how to add that in as well?