If and Date functions

Closed
Amanda - Jul 28, 2016 at 10:55 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Jul 28, 2016 at 01:59 PM
Hello,

I am trying to figure out how to format cells based on a certain category. I have two different due dates depending on the category they fall in. I will put them as Project 1 and Project 2. A project 1 category has a due date of 6 months after completion and a project 2 has a due date of 30 days after completion. So I will have the completion date and a category but need to figure out how to tell excel how to know the difference between the due dates.

3 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jul 28, 2016 at 12:21 PM
Amanda, Good morning.

You didn't explain what you call "to format cell"
Are you talking about Conditional Format?

If not, you can do your process like this:

A1 --> PROJECT CATEGORY
B1 --> COMPLETION DATE

Try to use:
C1 --> =IF(OR(AND(A1="Project 1",TODAY()>=EDATE(B1,6)), AND(A1="Project 2",TODAY()>=EDATE(B1,1))), "OVERDUE","VALID")

Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
That's the formula i needed but now I'm wondering if for the "true" "false" can i instead put a formula instead of just a word so it would say the date instead of if its overdue?
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jul 28, 2016 at 01:25 PM
Amanda,

I'm not sure I understood well your necessity now.

Please, give us some examples about what you have and how must be the result.

We're waiting your answer.
I'm pretty sure i'm making this more difficult than it needs to be!
So in cells G2:G10 i either have a 1 or a 2. and cells J2:J10 i have the completion date. I want cells K2:K10 to equal a date based on how long the deadline is. 1 would equal a deadline of 6 months and 2 would equal a 1 month deadline. Is that a possibilty?
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jul 28, 2016 at 01:59 PM
Amanda, Good afternoon.

Ohhh. Now everything is clear in your explanation.

If you have only 1 or 2 as values at G column, use this formula:
K2 -->
=IF(G2=1,EDATE(J2,6);EDATE(J2,1))


If you may have other values than 1 or 2, use this one:
K2 -->
=IF(G2=1,EDATE(J2,6),IF(G2=2,EDATE(J2,1),""))

Is this what you want?
I hope it helps.

Regards from Brazil.