Solved

help with IF Statement

Posted on 2014-04-01
11
126 Views
Last Modified: 2014-04-02
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
Comment
Question by:Jagwarman
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39969613
Hi,

pls try

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

Open in new window

Regards
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 39969616
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
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39969626
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
 

Author Comment

by:Jagwarman
ID: 39969634
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
 

Author Comment

by:Jagwarman
ID: 39969638
jigneshthar the answer to yuor question is yes.

Regards
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 39969648
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39969693
Hi,

Have you seen my corrected formula?

Regards
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39969781
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
 

Author Closing Comment

by:Jagwarman
ID: 39971510
thanks Rgonzo1971 that was exactly what I wanted.
0
 

Author Comment

by:Jagwarman
ID: 39971512
Thanks for trying robhenson
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39971627
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now