Sep 18 2020 12:49 AM - edited Sep 18 2020 02:03 AM
Hi,
I want to create the following logic with the attached table:
a. If Prep start month and the year is more than the Current month ( Row 37, Col H - Dec'20), then 0
b. If a is false, then only check Responsible person. If the responsible person is CEO, then 0
c. If a is false, then only check Responsible Person. If the responsible person is Product Manager then 1
d. If the end month and year (Col. F) is less than the current month, again 0
Attached excel for better reference. Please suggest the formula in column H. You can see the formula which I have used in the formula bar.
Sep 18 2020 01:33 AM
I think you're missing a few $ signs:
=IFS($D41>=H$37,0,$E41="CEO",0,$E41="Product Manager",1,$F41<H$37,0)
Apart from that: what should be returned if none of the conditions is met?
Sep 18 2020 04:08 AM - edited Sep 18 2020 04:35 AM
You get this,,
Note,, Only in Row 5 you get 1.
In range G9:J13, I've tested every IF.
For example formula in G9 is testing difference between Start Date & Current Date,,
=IF($H2-L$1=0,0,1)
and for the FALSE condition, I've used 1, is NEXT IF in original formula, check result in Col K.
Formula in H9 checks CEO, I9 for Project Manage & J9 is for Difference between End Date and Current date, check result in Col L.
You need to use tradition Nested IF instead of IFS:
=IF($H2-L$1=0,0,IF($I2="CEO",0,IF($I2="Pjoect Manager",1,IF($J2-L$1<0,0,"No Match"))))