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

# 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
• 4
• 3
• 2
• +1
1 Solution

Commented:
Hi,

pls try

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

Senior 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

Commented:
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 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

Author Commented:
jigneshthar the answer to yuor question is yes.

Regards
0

Senior 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

Commented:
Hi,

Have you seen my corrected formula?

Regards
0

Finance AnalystCommented:

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 Commented:
thanks Rgonzo1971 that was exactly what I wanted.
0

Author Commented:
Thanks for trying robhenson
0

Finance 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.