Time formula with conditions

[Andrew Skibbz]

Hi, I have two columns, one with a date and time (column A) and one with a status (column B).

I want a third column(Column C) to say if the date and time in column A is less than today's date and the status in column B is not equal to 'COMPLETED' then move the date to today's date at 07:00:00

Any help greatly appreciated.

Best,

Andrew

 

[steve the fish]

Ok so what if the conditions arent met? What is required then?

 

[Andrew Skibbz]

Good question Steve,

If the requirements are not met, then return original date.

Thanks,

andrew

 

[Andrew Skibbz]

Good question Steve,

If the requirements are not met, then return original date.

Thanks,

andrew

 

[steve the fish]

Try this:

=IF(AND(A2<>"",A2 < TODAY(),TRIM(B2)<>"COMPLETED"),TODAY()+TIME(7,0,0),A2)<today(),trim(b2)<></today(),trim(b2)<>

 

[Andrew Skibbz]

Brilliant, Thanks Steve - works a treat!

 

[Andrew Skibbz]

Steve,

If I want to add in an additional rule if A2 <>= "Completed" or "Cancelled", how would I write that formula?
Thanks in advance!
Andrew

 

[steve the fish]

Add it into the AND as its an AND condition. You dont want the cell to equal Completed and you dont want the cell to equal Cancelled.

=IF(AND(A2<>"",A2 < TODAY(),TRIM(B2)<>"COMPLETED",TRIM(B2)<>"CANCELLED"),TODAY()+TIME(7,0,0),A2)

 

[Fluff]

This is now a duplicate of https://www.mrexcel.com/forum/excel-questions/1094912-if-formula.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).