Calculate date after 7 business days

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
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.
Photo of Mike

Mike

  • 474 Points 250 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of Slider

Slider

  • 1,386 Points 1k badge 2x thumb
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.
If(month(today())=12,
WeekdayAdd(ToWeekdayN(todate(totext(1 &"/"& [Payday] &"/"& year(Today())+1))),7),
WeekdayAdd(ToWeekdayN(todate(totext(month(today())+1 &"/"& [Payday] &"/"& year(Today())))),7)

===============================================
WeekdayAdd
 (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