If formula to determined a value based on date quarter month

[virgosquirrel]

Hello, 

My Excel formula is IF(A2="","N/A",IF($B$1-A2<((365/12)*9),"Frequent"," Not frequent"))

which A2 is hire date cell and B1 is the current month cell. 

I've tried multiple if statement but couldn't get to the value Frquent & Not Frequent

Can someone please help with this ? 

Thanks!

[abacon]

@virgosquirrel What formulas have you tried in alteryx? I can see off the bat by your descriptions you are subtracting a date field from a month field which wouldn't work. You will need to convert the Current Month Cell to be the first date of the current month. What I would do is break down each section of the formula into a new tool to ensure I am getting the right answers, then put them into one. So you want to check if the difference between the current month cell and the hire date is less than a value, I would first make sure my calculation to get the difference between current month cell and hire date is accurate, then most likely it should be easy to get from there.

[DavidSkaife]

Hi @virgosquirrel 

You want to use something like the below:

IF ISEMPTY([Hire_Date])
THEN "N/A"
ELSEIF DateTimeDiff([Current_Month],[Hire_Date],'Days') < ((365/12)*9)
THEN "Frequent"
ELSE "Not Frequent"
ENDIF

However without seeing some sample data, and your expected output its hard to tell if this is correct

[virgosquirrel]

Thank you for the suggestion. This is my first time building a workflow so appreciate your input.

I just added the Current month cell to be the first date of the current month to start getting the if formula going. I will need to add the New Column for the output with the if formula. 

See the screenshot.

Can you please advise what I should use here to get to the Final output for 3 value based on the hiring date "N/A", "Frequent" and "Not frequent" ? 

Thank you so much!

[DavidSkaife · Accepted solution]

Hi @virgosquirrel 

Now we can see some of your data try this:

IF ISEMPTY([Hire_Date])
THEN "N/A"
ELSEIF DateTimeDiff(DATETIMEFIRSTOFMONTH(),[Hire_Date],'Days') < ((365/12)*9)
THEN "Frequent"
ELSE "Not Frequent"
ENDIF

Not sure why you're taking a month off the current month?

[virgosquirrel]

Thank you David, that is awesome,  I would not be able to figure that out.

I'm doing April month end reporting so I have to take a month off the current month which is May.

I tried the formula and it gave me the errors (see attached screenshot) 

Thank you so much!

[DavidSkaife]

Hi @virgosquirrel 

In your second formula replace DateTimeFirstofMonth() with the 'Current Month' field if thats what you're wanting to compare it against.

The conversion error is that one of your date fields isn't in the right format that Alteryx likes. You can perform a DATETIMEPARSE function on it to convert to the right format, or use the DateTime parse tool.

[virgosquirrel]

I got the if formula to work, thanks so much. 

However, I'm using the Datetimeparse function to convert to the right format and an error came up... In my hire date column, I have mm-dd-yyyy and mm/dd/yyyy as the date format, when I use the Date Time parse, I can only convert one format at a time to the Alteryx format, do you know if there's a way to use the Datetime parse tool to convert both of the format to the Alteryx format? 

Thank you so much again for your help on this, extremely helpful! 

[DavidSkaife · Accepted solution]

Hi @virgosquirrel 

Not using the DateTime parse tool. You'd have to use a Formula tool and a IF statement to run through the different formats, or a Multi-Field Formula tool. For example take a look at this thread for a similar idea - https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Convert-dates-with-different-d...

[virgosquirrel]

Thank you David. I'll look up the similar thread for the Date conversion issue. Appreciate your help!