If/And/ISDate/OR

Laura Bartlett

Hi All

I am trying to set up a status message based on a few variables. I can get them to work indidually, but when I bring them all together I get an InvalidData error.

The parameters are If FWA Required= YES and FWA attestation is Date and Date Attestation Form Signed is Date and Education Completion Date is Date. If all are true this is Completed

OR

If FWA Required is NO, Date Attestation Form Signed is Date and Education Completion Date is Date= If all are true this is Completed

If both either are False, "Not Completed"

Here is my Formula

=IF(AND([FWA Required?]@row = "yes", ISDATE([FWA Attestation Form Signed?]@row) = "true", ISDATE([Education Completion Date]@row) = "true", ISDATE([Date Attestation Form Signed?]@row) = "true"), "Complete", OR(IF(AND([FWA Required?]@row = "No", ISDATE([Education Completion Date]@row) = true), "Complete", "Not Complete")))

Let me know what I am doing wrong.

Leibel S

@Laura Bartlett

2 Things:

1. whenever you are using the ISDATE function you don't need to add ="true"
2. You can combine the 2 options for 'complete' into 1 using the OR function.

See below formula:

=IF(OR(AND([FWA Required?]@row = "Yes", ISDATE([FWA Attestation Form Signed?]@row), ISDATE([Education Completion Date]@row), ISDATE([Date Attestation Form Signed?]@row)),AND([FWA Required?]@row = "No", ISDATE([Date Attestation Form Signed?]@row),ISDATE([Education Completion Date]@row))), "Complete", "Not Complete")

Leibel S

@Laura Bartlett

2 Things:

1. whenever you are using the ISDATE function you don't need to add ="true"
2. You can combine the 2 options for 'complete' into 1 using the OR function.

See below formula:

=IF(OR(AND([FWA Required?]@row = "Yes", ISDATE([FWA Attestation Form Signed?]@row), ISDATE([Education Completion Date]@row), ISDATE([Date Attestation Form Signed?]@row)),AND([FWA Required?]@row = "No", ISDATE([Date Attestation Form Signed?]@row),ISDATE([Education Completion Date]@row))), "Complete", "Not Complete")

Laura Bartlett

OMG! This is great! Thank you so much!

I might reply again as I think i have some additional questions as to the order and a couple of 'whys'.

But for now, I'm stoked that you fixed it.

Laura Bartlett

Hi Leibel!

It has been a while, but the users are now starting to use the sheet. And unfortunately, if they mark FWA required as "yes" the formula shows as incomplete.

Can you help?

Laura Bartlett

OH jezz. Never mind. The FWA signed field was not a date field :(

I don't know how/when that was changed. But as soon as I narrowed down that field, I noticed it was a text field.