• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

help with IF Statement

Can an expert help with this please. The formula below should only give me a value if Today()-Y3 is -7, -6, -5 , -4, -3, -2 or -1 otherwise the cell should be blank but I am getting #Value


=IF(TODAY()-Y3>40000,"",IF(TODAY()-Y3<-7,"",TODAY()-Y3))

Maybe I should just ask can an expert provide me with a formula that will only return a number of -7, -6, -5 , -4, -3, -2 or -1  when I take today's date from cell Y3. [Y3 could have any date from today out 25 years.

Hope this is clear

Thanks
0
Jagwarman
Asked:
Jagwarman
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

=IF(AND(TODAY()-Y3>=-7,TODAY()-Y3<=-1,TODAY()-Y3,"")

Open in new window

Regards
0
 
Jignesh TharSenior ManagerCommented:
Can you explain logic of showing one of the values from -7, -6, -5 , -4, -3, -2 or -1  ? Do you have dates in Y3?
0
 
Rgonzo1971Commented:
Corrected
=IF(AND(TODAY()-Y3>=-7,TODAY()-Y3<=-1),TODAY()-Y3,"")

Open in new window


and as Positive number

=IF(AND(Y3-TODAY()<=7,Y3-TODAY()>=1),Y3-TODAY(),"")

Open in new window

Regards
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
JagwarmanAuthor Commented:
Hi Rgonzo1971 says it's missing a parenthesis so I put one in

=IF(AND(TODAY()-Y3>=-7,TODAY()-Y3<=-1,TODAY()-Y3),"")

now I get #Value
0
 
JagwarmanAuthor Commented:
jigneshthar the answer to yuor question is yes.

Regards
0
 
Jignesh TharSenior ManagerCommented:
Have you changed format of Y3 to date? This won't work if value cannot be convered to date. Try changing format using right click -> Format Cells -> Select "Date"
0
 
Rgonzo1971Commented:
Hi,

Have you seen my corrected formula?

Regards
0
 
Rob HensonFinance AnalystCommented:
Looking at your logic:

1) Today() - Y3 > 40000

=IF(TODAY()-Y3>40000,"",IF(TODAY()-Y3<-7,"",TODAY()-Y3))

If Y3 is a date greater than today  as per the question "Y3 could have any date from today out 25 years" the result of the above will always be negative so never greater than 40000.

As for dates in Excel, 40000 represents 6 July 2009.

2) Today() - Y3 <-7

=IF(TODAY()-Y3>40000,"",IF(TODAY()-Y3<-7,"",TODAY()-Y3))

Again, Y3 is a date and for the result to be negative then Y3 only needs to be 1 day ahead of today, a result of less than -7 would be from Y3 being today +14; at which point Excel refuses to co-operate because it doesn't like negative dates and gives #Value result.

3) Today() - Y3

=IF(TODAY()-Y3>40000,"",IF(TODAY()-Y3<-7,"",TODAY()-Y3))

Y3 is a date greater than today, so will always result negative and therefore as mentioned in point 2, Excel doesn't do negative dates.

What are you trying to acheive, a date or a number of days?

Your question states "when I take today's date from cell Y3". Your formula is taking cell Y3 from today, not the way round you have quoted.

Thanks
Rob H
0
 
JagwarmanAuthor Commented:
thanks Rgonzo1971 that was exactly what I wanted.
0
 
JagwarmanAuthor Commented:
Thanks for trying robhenson
0
 
Rob HensonFinance AnalystCommented:
Hi Jagwarmna, from the comment above about the missing parenthesis; I think you've put in the wrong place on here but maybe have put it in the right place in your workbook.

I believe it should be:

Move Bold bracket from this,
=IF(AND(TODAY()-Y3>=-7,TODAY()-Y3<=-1,TODAY()-Y3),"")

To here,
=IF(AND(TODAY()-Y3>=-7,TODAY()-Y3<=-1),TODAY()-Y3,"")

Previous formula had all within the AND brackets.

Thanks
Rob H
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now