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!

Workflow

Preparation

Accepted answers

davidskaife

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

Screenshot 2025-05-19 171645.png

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

Formula.yxmd

davidskaife

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-date-format-into-single-format/td-p/1193778

All comments

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!

Alteryx formula needed. png.png

davidskaife

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

Screenshot 2025-05-19 171645.png

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

Formula.yxmd

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!

Screenshot 2025-05-19 123504.png

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!

Screenshot 2025-05-19 135740.png

davidskaife

Hi @virgosquirrel

virgosquirrel

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703