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] = DateTimeAdd([Max_Date],-30,'days') and [Date] = DateTimeAdd([Max_Date],-90,'days') and [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 Book1.xlsx

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

Book1.xlsx

Date Time

Help

Accepted answers

ShankerV

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!!!

All comments

binu_acs

@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

MaxDate_1026.yxmd

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

date.yxmd

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

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

ShankerV

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!!!

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235