Formatter Features: Date Add/Subtract & Compare

  • 11 August 2021
  • 6 replies
  • 4524 views
Formatter Features: Date Add/Subtract & Compare
Userlevel 7
Badge +11

Formatter Features: Date Add/Subtract & Compare

Hey again, Zappers! Nick here, from the Community team. I’ll be taking you through some date-related features of Formatter by Zapier.

Dates come in many formats, and the various apps you're using may not line up nicely with one another. Sometimes you have time zones to contend with, or maybe you need to figure out how many days between two dates.

Today we’ll be looking at the following:

  • Date Add/Subtract

  • Compare Dates

Date Add/Subtract

This option can be found at:
Formatter by Zapier — Date/Time — Add/Subtract Time

ohqq4eVZs8VV8PO0hr5jh4ahimn1pvMxAOUmeT0j9YP8Yi4_pY8cAxiYEwJkFLEXK0BsLtKZRBFRaXzMHTIJ29A_4rVACTe2_2WkRd7Ua-TiriA21Ua2MeMgeWJlbD5cw7gRFF7o

What It Looks Like

mDyxMOO0YG3z-GNWHIhTDfUvvUe3Iwmer3smI618b8vJ12lkH7iqHo1uwruZeswLETN4eVV5lzblkxduFp9fXzlUp6Bm_HRBYfwaW-RafsdzmS0fqoFNHGkGgnzX-hIZgqwkw3dA

What It Can (and Can't) Do

Before you start to make assumptions or get your hopes up, let's briefly cover what is and is not possible with Add/Subtract Time.

Things you can do with this transform option:

  • Add/subtract months, days, or hours to a given date

  • Change the format for the finalized date

  • Specify the precise format for the incoming date

Things you can not do with it:

  • Process multiple dates at a time (line items not supported)

Some Ways to Use It

1) Specify an End Date Based on a Start Date
Let's say you're selling a subscription or membership and your trigger provides the start date and the amount of time the person has signed up for. You could use Add/Subtract Time to generate the end date:

Example: "Your subscription starts today [insert the date] and will end on [insert the output of the Formatter step]."

2) Calculate Times Based on Timezones
Your trigger step might provide a date/time for a single timezone but you need to output one or more different time zones (maybe based on a form submission). Depending what a person has specified, or you can get from metadata automatically, you could add the appropriate number of hours.

Trigger: Form Submission (for example)
Action: Formatter — Utilities — Lookup Table (to take form field — Eastern Standard Time — and output number of hours)
Action: Formatter — Date/Time — Add/Subtract Time (take initial date/time and add output of Lookup Table)
Action: App of Choice, including the output of that previous Formatter step.

3) Generate End Time of An Appointment/Consultation
Maybe you've got a booking app that lets people choose a start time for an appointment and then the length of time they'd like, but the app doesn't give you the end time. Much like Example #1 above, you could generate the end time of the appointment. 

Example: "Your appointment has been scheduled for [insert date from app]. It starts at [time from app] and ends at [output of Formatter step where you added start time plus length of appointment]".

Anything Tricky About It?

Like any Formatter step that uses dates/times you'll want to make sure that you use the From Format field if you're not getting the results you want. We try our best to parse the date but in some cases we may not be able to do it if the format is atypical. 

Make sure that you format the expression as specified. If you don't, you likely won't get the desired result.
Yes: -2 days, +1 month, +7 hours 25 minutes
No: - 2 days, - 2days, +1month, + 1 month, + 7hours 25minutes

Compare Dates

This option can be found at:
Formatter by Zapier — Date/Time — Compare Dates

udmy87PE_iICsAfK3BBBocqGRzo5wBHQfKBSDAQmIl98ZTygtI20C0Tof75XtKKWQrMe3JSnY7mzUi0TPXfRgWLlG0njrRIZMSkg3ZHDuZQKGChhvcTyU-58UffVS04EKjrZe3Q8

What It Looks Like

wv1NUjWZyO42nidZP8-wzg_Lhk0_fogmOHSGGaHzk3qOmVLYfZI1l39mER0QQl16kXqcgmrXXT4mUe6m9AkFxcCWigFw9bFQuDlm6eX60QfC9r2xSu9DwEUtvxtWyf1OU7Hh8qjw

What It Can (and Can't) Do

I'd hate for you to start building a Zap without knowing what is and is not possible with the Compare Dates transform.

Things you can do with this transform option:

  • Enter two dates and get the days, hours, minutes, seconds between them

  • Check whether two dates are the same

  • Specify the precise format for the start date and the end date

Things you can not do with it:

  • Compare more than two dates

  • Use line items to process multiple sets of dates

  • Adjust dates by using something like +1d

Some Ways to Use It

Filter on Whether Both Dates are the Same
There are times when you want to continue (or stop) a Zap based on whether two dates match. Since the Compare Dates step will tell you if they match, you can filter on that with a boolean (true/false) in the Filter step.

Example 1: What if you want to know whether a scheduled delivery date matches tomorrow's date, and if it does, you want to send a notification to the customer?

NOTE: Since Compare Dates does not yet support the adjustment of dates by using a modifier like +1d, you'd have to first use the Add/Subtract step (as shown at the beginning of this article.

gzcddLYjc-OQFc6sur-6cNpBbFIQMmMbHCMqU01dPaN2UjdI_h4UVfsQzjJUjQz1hOyFEI8L513INIrXNrZVPE1v5wK88VsRW24kf6Asp0NhTlMgoIB8nGMJB0P0j-LnQCv4T41n

You can see here that it's an exact match, since days, hours, minutes and seconds are 0.

M6fU3xA2IfxfDOLjqM7ItlmNoUFQI4Hwf6A7_NVAZXjVi12xe7q72ltG0RPUbcZPrk6_Nq5mR8fV3cWEu5TDbUl3bEDpupOi6uR2vTAwaAsUzbMy-4l-5owePlaQ10l4X5-_pmD6

Example 2: Maybe you don't want to allow someone to change their username if their creation date is the same as today's date. If the trigger provides the first date, you can use {{zap_meta_human_now}} as today's date.

CuxRy2v2qYUVozdzwOtBsNk94Y7JJcswTTg6U7vdmftUqJD9sy-ExC5opVh--cGyeQ0xT8GBJM70nOX4232NurESb9pmg-zC4UElwu0Y4YxIosCIae0K8gVMo16VLuBlT86ge5eu

When we test, we can see that the dates do match. We also see that the difference between them is 15 hours and 18 minutes, because one of them contains the time and the other does not (so we calculate it as the very beginning of the day).

ZGz0v70xvyIc9Av-DG-5tPOF__IM4Rl1_3lQjkM4d44kogxIl-yrDe5ZsYOzWjnAK9QArhTfYhxfzolk7P1C_yslVBPdD06_0UrpkXfYb6jgqzWfuRZW3LYgq0tu_LcEaKHH88Dt

Get the Number of Hours Between Now and Start Time
I'm using hours here but it could just as easily be days, minutes or seconds. Maybe you're sending out emails hyping an upcoming event and you want to know how much time there is at that exact moment, between the start time and when you send the email.

qOTFT_gDAUeEkeeqFsH9nJlel4ChTyq5uOVsG3LSoT1Kz8Frl8viFEIsCE9Y33qu7mRsl0JbToC7twW0dTXTjaW6CBSWZQs1bOXJeizNbMp49EPBIkoQs4VGrwBUFZTRtsRmFvXn

When we test the step, we get the output of 2. 

8UC4bQKKdahazm4ABenR2NgjLrtmQCKeTHUx1D2MdG13S55Soiu6les5_2WpuoClR9puamwjQ29O69gMBEqJZrlgc560L9L35nY2Q49PbesrOioafXD67-3LK8rtWQwWhvC3aIRb

We could map this into the body of an email:

"Are you excited?! The event starts in [output of Formatter step] hours! See you there :)"

Anything Tricky About It?

As mentioned in the section about what it can NOT do, you can't modify the date/time directly within the step. So if you want to compare a date with one that is X days/hours/minutes in the past or future, you'll need that extra step.

Just as with the Add/Subtract Time feature, if you're not getting the result you're expecting when you test, make sure to use the correct date formats here:

Z8-9MbORilx-pkZPWpTlgmOQtrvekrj-9sm2d_fEro7c7TJ8kuoFZiiVUT85ojVCEGT13JirAzDWocCf2jGtqr5cNcuXYfz-ZpTRvYBxD2L5XjI5Fiqb_cwpUJkq14ln3ewHE-NC

Coming Up in the Series

Next up in the Formatter Features series we'll be taking a look at currency + number formatting
and how you can use these features in your Zaps. Until next time!


6 replies

Userlevel 1

Would it be possible to get the “Next Business Day”?

So adding one day but skipping saturdays/sundays.

Userlevel 7
Badge +11

Hi @TMarkwell!

I don’t know of a way to do that in one step 

The way I’ve approached this in the past is to use a Lookup Table that checks which day the Zap is triggering, then outputs the number of days I want to add:

1. Format the date the Zap triggered, into just the day (more info)

 

2. Put that into the Lookup Table to give me a number of days to add (lookup tables)

 

3. Use the Date Add/Subtract feature in Formatter

 

If you’re looking to delay something until the next business day, it’s similar and outlined here.

NOTE: It doesn’t matter whether you use something like Wed or Wednesday, as long as the output of your first Formatter step matches what you’ve set up in your Lookup Table (in the left-hand set of values).

Hope that helps!

Userlevel 1

Hi @TMarkwell!

The way I’ve approached this in the past is to use a Lookup Table that checks which day the Zap is triggering, then outputs the number of days I want to add:...

Thanks for your reply! 

I have also discovered zapier allows Code actions as well, so we can provide a small bit of javascript for this particular use case.

Something like this should do the trick...

var startDate = "2021-10-01";
startDate = new Date(startDate)
var endDate = "", noOfDaysToAdd = 1, count = 0;
while(count < noOfDaysToAdd){
endDate = new Date(startDate.setDate(startDate.getDate() + 1));
if(endDate.getDay() != 0 && endDate.getDay() != 6){
count++;
}
}

 

Userlevel 7
Badge +11

That’s a nifty workaround! Thanks for sharing it here, @TMarkwell. :)

How do I use a Hubspot property (that stores the number of days I wish to add to a date) in the add/subtract expression?

Is there any way to add/subtract from a date/time in and display that in an email?

 

For example, I want to schedule a monthly email and reference the prior month in each email subject. How can I achieve that?

Reply