Forum Discussion
looking for help with an Excel function
I am looking for a function based on certain criteria. I would like to have a date populate in "Process: Check due date" Column when the State ISO populate. the Criteria would be in the Quickpay rule column per State . EX. TX pays 6 calendar days from Termination date, will need to add +6 to termination date column for every TX state employees.
One possible way to have a date populate in “Process: Check due date” column based on certain criteria is to use a combination of IF and DATE functions. The IF function can check if the State ISO matches a certain value, and the DATE function can add a number of days to the Termination date based on the Quickpay rule.
For example, if you want to populate the date for TX state employees who pay 6 calendar days from Termination date, you can use:
=IF(A2="TX",DATE(YEAR(B2),MONTH(B2),DAY(B2)+6),"")
where A2 is the cell that contains the State ISO and B2 is the cell that contains the Termination date.
This formula will return a date that is 6 days after B2 if A2 is “TX”, and an empty string otherwise.
You can copy this formula down to other rows and change the criteria for other states as needed.
I hope this helps.
- Thank you, that really help! so the data will populate automatic with macros, so the states will be in different cells, How would I do multiple states for same formula? its a total of 18 states.
IF(A2="TX",DATE(YEAR(B2),MONTH(B2),DAY(B2)+6),"")If you want to apply the same formula for multiple states (make states for Coditions :), you can use the OR function inside the IF function. The OR function returns TRUE if any of the arguments is TRUE, and FALSE if all of the arguments are FALSE.
For example, if you want to add 6 days to the date in column B if the state in column A is TX, CA, NY or FL, you can use this formula:
=IF(OR(A2="TX",A2="CA",A2="NY",A2="FL"),DATE(YEAR(B2),MONTH(B2),DAY(B2)+6),"")
This formula will check if A2 is equal to any of the four states, and if so, it will return the date in B2 plus 6 days. Otherwise, it will return an empty string.
You can add more states (conditions 🙂 to the OR function by separating them with commas. For example, if you want to include 18 states (conditions), you can use something like this:
=IF(OR(A2="TX",A2="CA",A2="NY",A2="FL",A2="WA",A2="OR",A2="NV",A2="AZ",A2="CO",A2="NM",A2="UT",A2="ID",A2="MT",A2="WY",A2="ND",A2="SD",A2="NE",A2="KS"),DATE(YEAR(B2),MONTH(B2),DAY(B2)+6),"")
Hope I could help.
I know I don't know anything (Socrates)
