Previous Topic Next Topic

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

Answer 1 / 3
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
Amanda
Jul 28, 2016 at 01:01 PM
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?
Answer 2 / 3
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.
Amanda
Jul 28, 2016 at 01:29 PM
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?
Answer 3 / 3
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.

Similar discussions

If cell A1 has text then cell B2 has today's Date
Sasha -
ac3mark -

1 response