Nested IF formula that ignores blank cells

Hello,

I'm in need of some help with this one and am ready to give up!!

I am trying to determine progress status of certain deliverables at a given date.

If there is a date in cell M to T, I would like column AP to show the value of that progress dependent on the date entered in cell AP1 . So far, I get either 0 or 1!!

Current best effort is:

=IF(OR(T3="",S3="",R3="",Q3="",P3="",O3="",N3="",M3=""),"",IF($AP$1<T3,1,IF($AP$1<S3,0.925,IF($AP$1<R3,0.75,IF($AP$1<Q3,0.7,IF($AP$1<P3,0.6,IF($AP$1<O3,0.45,IF($AP$1<N3,0.25,IF($AP$1<M3,0.05,0)))))))))

Help.JPG

Thank you in advance for any help,

Simon

Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you John, I have added a demo file.

thank you for your help xlnitwit. You are correct, the date should read 2018 and I have uploaded a revised sheet. The formula you proposed returns only 0 for me.

Very much appreciate your time

Yes, I can confirm that M:T are date format in my file

It works!! My mistake. Thank you so much for your help!!