Formula tool and dates

[gottaask08]

Hi, I am trying to create a new field named [Values] using the formula tool from [Date] and [Max_Date] field.

I would like one case statement that will result in values 1, 2,3,4,5 

IF [Date]>= DateTimeAdd([Max_Date],-14,'days')) and [Date]<= [Max_Date] then 1

ELSEIF [Date]>= DateTimeAdd([Max_Date],-30,'days') and [Date]<= [Max_Date] then 2

ELSEIF [Date]>= DateTimeAdd([Max_Date],-90,'days') and [Date]<= [Max_Date] then 3

ELSEIF [Date]>= DateTimeAdd([Max_Date],-120,'days') and [Date]<= [Max_Date] then 4

ELSE 5

ENDIF

Unfortunately there is an overlap between 1,2,3,4,5 and I am not sure how to do this. Any help is appreciated

[binuacs]

@gottaask08 Your formula seems to be working fine. Are you seeing any issues in your output?

IF [Date]>= DateTimeAdd([Max_Date],-14,'days') and [Date]<= [Max_Date] then 1

ELSEIF [Date]>= DateTimeAdd([Max_Date],-30,'days') and [Date]<= [Max_Date] then 2

ELSEIF [Date]>= DateTimeAdd([Max_Date],-90,'days') and [Date]<= [Max_Date] then 3

ELSEIF [Date]>= DateTimeAdd([Max_Date],-120,'days') and [Date]<= [Max_Date] then 4

ELSE 5

ENDIF

[gautiergodard]

@gottaask08 I dont think you need the second part of your if statement, but in any case the formula seemed to be working.

Is your data type a "date" data type?

See attached

[gottaask08]

The formula is working fine but I think I did not ask my question well. The intention is to have the values field show if a date falls in past 14, past 30, past 90, past 120 or 120 or more days.

That is to say if a date is between 2022-10-12 and  2022-10-26 (the last 14 days) be value =1  

if a date is between 2022-09-26 and  2022-10-26 (the last 30 days) be value = 2

etc

 Any help is appreciated

[gautiergodard]

The formula in the workflow I attached seems to match your scenario... sorry if im missing something.

[gottaask08]

Not quite. 2022-10-12 falls in both the past 14 days and the past 30 days. Not sure if I am overthinking this but any help is much appreciated :)

[gautiergodard]

So in that scenario just to be clear, what number would you expect assigned?

[ShankerV · Accepted solution]

hi @gottaask08 

Just use select tool/date time tool and change the data type to date.

Then perform your function, your output will be fine.

  

Hope it helps!!!