Date Time Function - Between

[vaibhav_jain]

Hi, I have attached workflow. Using between conditions for creating new column called Date1. 

If date falling between 25th April 2017 - 10th May 2017 then = 31st May 2017

If date falling between 11th May 2017 - 24th May 2017 then =15th June 2017

If date b/w 1st May - 19th May 2017 then =20th May 2017

If date b/w 20th May 2017 - 30th May 2017 then = 20th June 2017

Any suggestions.

[MarqueeCrew · Accepted solution]

@vaibhav_jain,

There are overlapping date ranges.  In one expression, you say that May 1st is May 31st and in another you state that May 1st is May 20th.  The first two expressions conflict with the second two expressions.

IF 
     [Date]>="2017-04-25" and [Date]<= "2017-05-10" THEN "2017-05-31" ELSEIF
     [Date]>="2017-05-11" and [Date]<= "2017-05-24" THEN "2017-06-15" 
ELSE
     [Date] 
ENDIF

Cheers,

Mark

[SeanAdams · Accepted solution]

Hey @vaibhav_jain - happy Tuesday!

what is the logic that drives your outcome on the date?

The reason that I'm asking is that there are some clever things you can do with dates rather than doing if-then formulae on dates.   

For example:

- if you're trying to get to the end of a 45 day sales cycle, you could build a table that has your date ranges in it (in a text-input) and then situate your date in that range

- if you're trying to round up to the next Monday - that's really easy in a single formula

Let us know what transformation you're looking to do, and send over some sample data (e.g. 20 dates or so; with the output you want), and we can work together to get you there.

cheers

Sean

[vaibhav_jain · Accepted solution]

Thanks for the revert.

Have resolved the query !!!