Forum Discussion

AndreonnaGarret's avatar
AndreonnaGarret
Qrew Assistant Captain
8 years ago

Create a Collection Schedule by day of the week

I am trying to build a table for a specific task that I complete. I collect a set of data from some customers once or twice a week. Some are on Monday and Friday, some are Monday/Thursday, some are ju...
MCFNeil's avatar
MCFNeil
Qrew Captain
8 years ago
There are a few parts to this, or for each formula.  This is assuming that is a week to week thing, and old dates for 'estimates' are not needed.  You will also want this to be a rolling weekly, so on friday, you see next mondays date if needed.

QB has a formula that will give you the next date on the given day
NextDayOfWeek (Date d, Number n)
(Sunday=0, Monday=1, etc.)

So for your Monday field it would be [Monday Collection Date]

NextDayOfWeek([Last Collection Date], 1)

Then account for if the Monday date is needed;

If(Contains([Collection Days], "Monday"), 
NextDayOfWeek([Last Collection Date], 1), 
null)

....repeat for all the days that collections are possible (mon-fri)....

Then have a master field for [Next Collection]

Min([Monday Collection Date], [Tues Collection Date], ....)

...I'm thinking we will need some more logic to account for times that the 'last' collection date is over a week ago...  i.e. if you miss collection days.
  • AndreonnaGarret's avatar
    AndreonnaGarret
    Qrew Assistant Captain
    8 years ago
    This worked perfectly! Thank you. 

    Yes you are correct as well that there would need to be additional logic if a date is missed. 

    For now I can just filter my report to only show those that have a [Next Collection Date] on or before today. Then I can follow up on ones missed, and once I've collected for the day they will fall off of the report.