Populate Cell Based on Multiple Criteria including day of week

Question

I am trying to populate Column E to determine on-time/late notice based on day-of-week request was provided and number of days notice was provided before date needed.

Column A: Date Requested
Column B: Date Needed
Column C: # of Days Notice
Column D: Day of Week Notice Provided
Column E: On-Time/Late

    A         B         C         D         E
5/1/2016  5/10/2016     9      Sunday
5/3/2016  5/11/2016     8      Tuesday
5/5/2016  5/15/2016     10     Thursday
5/11/2016 5/12/2016     1      Wednesday
5/12/2016 5/15/2016     3      Thursday  

The criteria for determining if something is on-time or late is as follows:

Column G: Day of Week
Column H: # of Days Required for On-Time Notice

     G          H
   Sunday       8    
   Monday       7  
   Tuesday      6    
   Wednesday    12  
   Thursday     11   
   Friday       10 
   Saturday     9   

Example: If the notice was provided on Thursday but only 6 days in advance, I need Column E to show "Late". However, if the request was on Thursday and 12 days in advance, I need it to show "On-Time".

Anyone with any good ideas? I'm sure it can be done, but my excel skills aren't that advanced.

Thanks for the help!

Answer 1

Assuming you started from second row

use this formula in E2 copied down

=IF(AND(D2=$G$2,C2>=$H$2),"On time",IF(AND(D2=$G$3,C2>=$H$3),"On time",IF(AND(D2=$G$4,C2>=$H$4),"On time",IF(AND(D2=$G$5,C2>=$H$5),"On time",IF(AND(D2=$G$6,C2>=$H$6),"On time",IF(AND(D2=$G$7,C2>=$H$7),"On time",IF(AND(D2=$G$8,C2>=$H$8),"On time","Late")))))))

Regards