Need the Formula to be aligned

[adnanmir]

Hi Excel Champs,

Can you please help me write this formula properly so all the IF conditions can work properly?

The formula is placed in the Status Tab " =IF(D2="Not Required in Current FY","No Visit Required",IF($A2<>TODAY(),"Needs Scheduled",IF(ISNUMBER(B2),"Work Scheduled ",IF($C2=B2,"Completed",IF($C2<=D2,"Completed Earlier", "Completed with Delay"))))) "

the details of each scenario are mentioned in the pic here

Final sheet should look like this please

 

[Alex Blakenburg]

Not exactly sure what you are after but this might get you part of the way there.
You need to decied what you want if A2 = Today and no other boxes are filled out, it currently returns completed.

=IF($D2="Not Required in Current FY","No Visit Required",
       IF(AND($A2<>TODAY(),$B2=""),"Needs Scheduled",
            IF(AND(ISNUMBER($B2),$C2=""),"Work Scheduled ",
                IF($C2=$B2,"Completed",
                    IF($C2<=$B2,"Completed Earlier",
                         "Completed with Delay")))))

 

[adnanmir]

Thanks, Alex, For coming back with your feedback, Let me explain if you can help based on that.
Firstly I want to achieve the results in "status" column E based on the information showing up in columns B, C, and D.
The scenario is when I open the file, the formula check for today's date and see if there is any value in Col B as to match against Col A. If there is no Value in it then, immediately it displays Needs Schedule.
Once that is satisfied, let's say I schedule a date for work to happen by assigning a date in col B and once there is data in col B, it displays the status as Work Scheduled.
Once the work is scheduled and done on a specific date in Col C it will evaluate against Col B and displays if it was Completed Earlier" or "Completed with Delay.
Lastly if in Col D there is a statement Not Required in Current FY, it will just display No Visit Required.

This is the key I want to achieve from this formula, I am not sure if I m going correctly or not , surely your help will make it better for me. I m not an expert just an occasional user of excel for work purposes. Looking forward to your support and help.

Please let me know if still my explanation does need more clarification for your to assist.

 

[Alex Blakenburg]

Did you try the formula in my post #2 ?
Which scenario didn't it work for ?

 

[adnanmir]

Did you try the formula in my post #2 ?
Which scenario didn't it work for ?

i try it now and let you know

 

[adnanmir]

i try it now and let you know

It works like a charm, mate you made my day! Champy Boy GOD BLESS YOU!

 

[Alex Blakenburg]

You're welcome. Glad I could help.

 

[adnanmir]

You're welcome. Glad I could help.

Your certainly did helped me, Can i ask you a question can the similarly scenario be built in MS Access. IF so can you help with that too ?

 

[Alex Blakenburg]

There is separate notice board for MS Access, if you want to ask there but I can't see any reason you couldn't do it. You would just use Column/Field names instead of Cell references.