IF statement involving DATE

Hi,

I'm trying to add a column to indicate whether the "Effective Date" is in the current or future month (i.e. TRUE) or previous month (i.e. FALSE), using last day of previous month's date in the formula. The Excel formula looks like this:

=IF(M2>DATEVALUE("10/31/18"),TRUE,FALSE)

I want to:

1. set the date value to always check for last day of previous month instead of having to change it each time the report is run in Alteryx.

2. find out how to convert the Excel formula (bearing in mind point 1) in Alteryx?

Thank you!

Reporting

Accepted answers

MarqueeCrew

DateTimeStart() is when you ran.

First of month could be expressed as:

DateTimeFormat(DateTimeStart(),"%Y-%m-01")

Cheers,

Mark

All comments

sumitiiest

Hi @BJSOON ,

Please find attached the alteryx workflow for your requirement.

I have used the below formula to get last day of previous month.

date calc.PNG

Thanks,

Sumit

Date Condition.yxmd

BJSOON

Hi Sumit, this works! Thank you so much! I have another question on date formula. I tried the following formula to get all the June records, however, it keep missing the 1st and 2nd June records which exist in my input file. It only returned records for 3rd June and onwards. What did I miss?

DateTimeAdd(DateTimeFirstOfMonth(),-0,'months')

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335