Excel formula help please

  handful 14:49 20 May 2008

I am trying to create a formula or conditional format which does the following for me.

Say A1 is a date entered for the completion of a job. I want to conditional format this cell so that it turns red after a period of time say 4 weeks. This part is ok but I also want to return to the pre formatted state if cell A2 has a date of payment received entered in it. Therefore it is like a conditional format with an IF statement thrown in. I'm sure there is a reasonably easy way of doing this but haven't been able to work it out yet. Could anyone (vog maybe!) help. Many thanks in advance.

  VoG II 14:55 20 May 2008

I'm not sure whether you want A1 4 weeks before or after today. I've assumed before and used 30 days instead of 4 weeks. Use Firmula Is and


  handful 15:22 20 May 2008

Thanks for the lightning response VoG! I think this is very close to what I'm looking for but can't get it to work properly. I need A1 to change after 30 days so for the sake of the argument, say a job had been completed on 1st May I want it to change on 31st. A2 would remain empty until payment is received but once a date is entered in A2 to confirm a payment has been madeI want A1 to revert to it's original format. Sorry if I'm being a bit thick but I'm struggling to get my head round this one!

  VoG II 15:30 20 May 2008

I believe that is exactly what my CF formula does.

If today was 1 June then the condition TODAY()-A1>30 would be met. If A2 was blank (="") then the CF would be invoked. If A2 contained a date (or indeed anything) then the CF would not be invoked.

  handful 15:40 20 May 2008

Sorry VoG, you are right, as always!! Thanks a million for the help.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor 9 Lite review

HomePod review

Les meilleurs logiciels de montage vidéo gratuits (2018)