Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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