Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# help with IF Statement

Posted on 2014-04-01
Medium Priority
132 Views
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
Question by:Jagwarman
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +1

LVL 53

Expert Comment

ID: 39969613
Hi,

pls try

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

LVL 7

Expert Comment

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 53

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 39969626
Corrected
``````=IF(AND(TODAY()-Y3>=-7,TODAY()-Y3<=-1),TODAY()-Y3,"")
``````

and as Positive number

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

Author Comment

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

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

Regards
0

LVL 7

Expert Comment

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 53

Expert Comment

ID: 39969693
Hi,

Have you seen my corrected formula?

Regards
0

LVL 33

Expert Comment

ID: 39969781

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

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

Author Comment

ID: 39971512
Thanks for trying robhenson
0

LVL 33

Expert Comment

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month7 days, 20 hours left to enroll