Forum Discussion

Brass Contributor

Feb 14, 2018

Formulas and Functions

Please Help - I'm no Excel expert!! I am trying to improve a log of audit actions, more specifically their timescales for completion. This is the formula I currently have in Cell M2: =I...

Formulas and Functions

Brass Contributor

Hi Ismael

Thanks for your reply but this hasn't produced the outcome I was looking for but it has stemmed an idea.

Thanks!

Ismael Pimienta

Copper Contributor

Feb 15, 2018

Please share the outcome you were expecting. I used the NOW function to extract current date but that can easily be pointed to a cell.

Aaron Lockett
Brass Contributor
Feb 15, 2018
The outcome I'm expecting is that Column M to turn red if dates specified in Column K lapse over the current date. Regardless whether a date has been entered into Column L.

Once I figure it out I will post the formula.
- Ismael Pimienta
  Copper Contributor
  Feb 15, 2018
  If you want column M to turn red (R) when the K date has lapsed (which it will always do over time) regardless of what's in L, then this should work:
  
  =IF(K2="","D",IF(L2="",IF(K2<NOW(),"R","A"),IF(K2<NOW(),"R",IF(K2<L2,"R","G"))))
  - Aaron Lockett
    Brass Contributor
    Feb 15, 2018
    No this hasn't worked.
    
    If the completion time scale lapse over the current date it should go red, if it is completed (Column L less than todays date) It should go green.
    
    I only want Column M to go red if the date specified in Column K or L lapse over the current date.
    
    If there is no date in Column L and the date in Column K has NOT lapsed over the current date then it should be Amber.
    
    If the date in Column L is less than Column K it should go Green.
    
    I apologise if I haven't explained this as best as I could have.

Share

Resources