Best answer

Return the next March 1st date

  • 22 September 2022
  • 3 replies
  • 20 views

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.

icon

Best answer by GetUWired 23 September 2022, 15:08

View original

3 replies

Userlevel 7
Badge +14

Hi @CNVC 

Good question.

A Code step would be best suited for this.

Userlevel 7
Badge +12

@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
 


 

 

Userlevel 7
Badge +12

@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: 


 

 

Reply