Formula Needed

Not so formula savvy

I need a formula to produce a "YES"

for when "CM Status" column is "Complete"

And the "NPR Completion Date" column is from a certain date (18 months out - example from 12.01.23 to today)

@Paul Newcome ☺️😆

Paul Newcome

This would be an IF/AND/OR combo.

Try something along the lines of

=IF(AND(OR([CSM Status]@row = "Complete", [CS Status]@row = "N/A (No Action Required)"), [NPR Completion Date]@row >= DATE(2023, 12, 01), [NPR Completion Date]@row <= TODAY()), "YES")

The above is hardcoded for 1 December 2023. If you needed that to be dynamic (such as 18 months), we can make that modification as well.

Paul Newcome

@Not so formula savvy Can you clarify on the date piece? Do you mean when the date is within the next 18 months, or do you mean when the date is more than 18 months away? Or are you meaning in the past instead of in the future?

Not so formula savvy

i need to be able to have a yes from a certain date (like 12.01.23) through today's date….because i want to be able to adjust the fx when the next fiscal year starts on 7.1.25, i can then change the date (12.01.23)..

hope that made sense?

Thanks Paul

Paul Newcome

My apologies. I am still not following. It has been a long week. Do you have a screenshot for reference?

Not so formula savvy

I apologize for not exlaining well, I neglected to clarify correctly:

I need to be able to get a "YES" for "CM Status" column that is either "Complete" or "N/A (No Action Required)", when "NPR Completion Date" column has a date from 12.1.23 to today's date.

I definitely didn't explain it clearly!

Hope this made sense?

ex.PNG

ex1.PNG

See screen shots.

Paul Newcome

This would be an IF/AND/OR combo.

Try something along the lines of

=IF(AND(OR([CSM Status]@row = "Complete", [CS Status]@row = "N/A (No Action Required)"), [NPR Completion Date]@row >= DATE(2023, 12, 01), [NPR Completion Date]@row <= TODAY()), "YES")

The above is hardcoded for 1 December 2023. If you needed that to be dynamic (such as 18 months), we can make that modification as well.

Not so formula savvy

This is perfect….I don't know what I would do w/out yr help….🤗. I do try to tweak your fx for other scenarios, but sometimes, i just can't figure out how to get it to work properly.

Thanks always Paul!!!

Paul Newcome

Happy to help. 👍️