Formula help: Date of Service... from today

Hi everyone! New to this formula stuff, and can't look at google SQL examples anymore! 🤤

I'm attempting to add 3 new "From Today" formula columns. 1-Served Within 90 Days; 2-Served 91 to 180 days; 3-Served more than 180 days. Don't know if I should start with a WHERE clause, an IF clause... some date clause! Just UGH.

Thank you all in advance.

Date Time

Accepted answers

Jonathan-Sherman

Hi @MaryCann,

I would use three formulas:

<= 90 Days

IF DateTimeDiff(DateTimeToday(),[Date],'days') <= 90
THEN '1'
ELSE '0'
ENDIF

90 to 180 Days

IF DateTimeDiff(DateTimeToday(),[Date],'days') > 90
AND DateTimeDiff(DateTimeToday(),[Date],'days') <= 180
THEN '1'
ELSE '0'
ENDIF

Over 180 Days

IF DateTimeDiff(DateTimeToday(),[Date],'days') > 180
THEN '1'
ELSE '0'
ENDIF

Kind regards,

Jonathan

20220407 - DateBuckets.yxmd

All comments

Qiu

@MaryCann
in MS SQL Server, you can do this

https://stackoverflow.com/questions/17717515/how-to-query-for-todays-date-and-7-days-before-data

In alteryx,

We can use DateTime Function,

DateTimeDiff(Date1, DateTImeToday(), 'Days')

allwynthomas24

Hey @MaryCann ,

Can you please provide sample data set. Regards.

Jonathan-Sherman

Hi @MaryCann,

I would use three formulas:

<= 90 Days

IF DateTimeDiff(DateTimeToday(),[Date],'days') <= 90
THEN '1'
ELSE '0'
ENDIF

90 to 180 Days

IF DateTimeDiff(DateTimeToday(),[Date],'days') > 90
AND DateTimeDiff(DateTimeToday(),[Date],'days') <= 180
THEN '1'
ELSE '0'
ENDIF

Over 180 Days

IF DateTimeDiff(DateTimeToday(),[Date],'days') > 180
THEN '1'
ELSE '0'
ENDIF

Kind regards,

Jonathan

20220407 - DateBuckets.yxmd

MaryCann

Thank you everyone! @Jonathan-Sherman - worked great!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703