Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

I am trying to return a date of March 1 of the next year unless today is earlier in the year than March15.

So if today is 09/01/2022 then I would return 03/01/2023.

If today is 02/01/2023 then I would also return 03/01/2023

That means I can’t just increment the year by one year in all cases.

I extracted the current month and current year in previous steps, then tried this spreadsheet formula:

if(<current month> >= 3, "03/15/" &  <current year> + 1, "03/15/" & <current year>)

The problem is, the formula blows up when concatenating a string and a number, and there seems to be no function to cast a number to a string (and it won’t do it automatically). 

And since spreadsheet formulas have no date functions, there seems to be no reasonable way to combine simple logic and date transformations.

Questions:

  1. Is there some simpler way of approaching this (without code)?
  2. Is there some way in a spreadsheet formula to combine a string and a number?
  3. Why didn’t they include any date functions in the spreadsheet formula!!!!!!

I may end up writing some code for this, but for maintainability when I leave this project I prefer to keep it all in native actions if possible.

Hi @CNVC 

Good question.

A Code step would be best suited for this.

@CNVC 
 

You would likely need to use 3 steps to calculate this without any code but it could be done like so: 

First a date time formatter that compares todays date to march 15th. Leaving off the year just uses the current year. You will get back one important value… ‘dates swapped’. When dates swapped is false that means it is before the end date value (march 15).. When dates swapped is true then today is after the end date. 

One thing to consider is what to do on 3/15… With this solution, the end date value will be at midnight but if the current time is after midnight, the dates swapped value will be true so this solution will output 3/1/{next year}. If the times are exactly the same then it will output 3/1/current year



From there you can pass the output into a look up table to swap a formula whether date swapped is true or false.. Essentially, do you need to add or subtract days to get to 3/15
 


Lastly, pass that output into a date time formatter to add/subtract days
 


 

 

@CNVC 

However, if you really wanted to do it with spreadsheet formulas (or in 1 step without a code block) you could use Google Sheets to accomplish it, you would just end up with a rather long spreadsheet at some point as every time you want to compare date values you have to create a spreadsheet row (or i suppose you could use the update row action).

Here is that formula for copy: =If(DATEVALUE(INDEX(A:A,ROW(),1))>DATEVALUE(INDEX(B:B,ROW(),1)),"03/01/"&Year(today())+1,"03/01/"&Year(today())) 

You can read more about the use of index here: 


 

 

Terms & Conditions