Question

Calendar Date Entry Avoiding Weekends? Google Calendar & Quickbooks

  • 2 April 2020
  • 2 replies
  • 142 views

Hi folks,

I have a Zap that extracts info from an email generated by our website booking system and places it into a new Quickbooks invoice.

We clean items for customers, so there are three important dates.

  1. We courier customers an empty box on PACKAGING DELIVERY DATE
  2. We courier the full box from customers on COLLECTION DATE
  3. We return items to customers on RETURN DATE

Right now, the web booking system generates COLLECTION DATE only (Monday to Friday), which we extract from the email and then use Zapier formatters to manipulate and insert the other dates as follows:

  1. For PACKAGING DELIVERY DATE we use “-2 days”
  2. For RETURN DATE we use “+8 days”

It works ok, but I’d like to know if there’s a way that these two dates can AVOID weekends? So if someone right now books online for a Tuesday collection, -2 days would be a Sunday, and so on. Our courier only operates Monday-Friday. So, we have to adjust every Quickbooks invoice manually if any of the formatted dates fall onto a weekend.

Hope that makes sense and there’s relatively easy solution?

Thanks in advance.


This topic has been closed for comments

2 replies

Userlevel 7
Badge +10

Hi Conor,

Thanks for the workflow challenge!

It’s not obvious but I’ve been around long enough that I’ve learned creative workarounds :)

Would this be accurate for your desired outcome:

Packaging

Monday: -2 days would be Saturday, so you’d want Friday instead (-3 days)
Tuesday: -2 days would be Sunday, so you’d want Friday instead (-4 days)
Wednesday: -2 days would be Monday (that’s fine)
Thursday: -2 days would be Tuesday (that’s fine)
Friday: -2 days would be Wednesday (that’s fine)
Saturday: -2 days would be Thursday (that’s fine)
Sunday: -2 days would be Friday (that’s fine)

Return

Monday: +8 days would be Tuesday (that’s fine)
Tuesday: +8 days would be Wednesday (that’s fine)
Wednesday: +8 days would be Thursday (that’s fine)
Thursday: +8 days would be Friday (that’s fine)
Friday:  +8 days would be Saturday, so you’d want Monday instead (+10 days)
Saturday: +8 days would be Sunday, so you’d want Monday instead (+9 days)
Sunday: +8 days would be Monday (that’s fine)

I assume you’re probably using a Formatter step that adds/subtracts a number of days from your dates. Here’s an animated GIF to demonstrate what I’ve got in mind:

https://cdn.zappy.app/40bb305b6273329e5273bb42d8c15b15.gif

So you format the date into just the day of the week (using ddd as the format) then you have a lookup table that outputs the appropriate number of days, which you then map into the formatted step where you’re adding or subtracting days. In the lookup table you can just add the days that are NOT the standard, then add your standard number of days as the fallback value. That way only the days that cause problems will output those modified numbers.

Does that make sense? Please let me know if you have any other questions or if anything is unclear. I'm happy to help!

Userlevel 7
Badge +10

Hi @Conor! This was a little while ago but I wanted to check in with you to see if this had ultimately helped you out, or if you came up with another way of solving the problem. If you still need help with this, please let me know!