Previous weeks - IF statement

[bobcunnie]

Can you help?

I want to use an IF function to look up a date field and if that date is within the last 2 weeks then the words "2 weeks" appears.


I have tried this but it is not working - can anyone advise...


=IF(CK8>NOW()-(2*7),"2 Weeks","")


Thanks

 

[Jonmo1]

That should work..

Although you might try using TODAY instead of NOW..

Is CK8 actually a real date, or just a text string that looks like a date?

What does this return
=ISNUMBER(CK8)

 

[bobcunnie]

CK8 returns
=ISNUMBER(CK8)

TRUE

It does not work

 

[Jonmo1]

Describe "does not work"

Do you get an Error? What error?
Is the wrong result given, what result WAS given, what result did you expect?
What Date is in CK8?

 

[bobcunnie]

This is what I get

Migration Date</SPAN>	Completed within last 2 weeks</SPAN>
31/03/2012</SPAN>
01/06/2012</SPAN>
30/06/2012</SPAN>	2 Weeks</SPAN>
19/11/2012</SPAN>	2 Weeks</SPAN>
30/09/2012</SPAN>	2 Weeks</SPAN>
31/07/2012</SPAN>	2 Weeks</SPAN>
31/07/2012</SPAN>	2 Weeks</SPAN>
01/12/2011</SPAN>
01/12/2011</SPAN>
28/11/2011</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

Completion Date </SPAN>	Completed within last 2 weeks</SPAN>
31/03/2012</SPAN>
01/06/2012</SPAN>
30/06/2012</SPAN>	2 Weeks</SPAN>
19/11/2012</SPAN>	2 Weeks</SPAN>
30/09/2012</SPAN>	2 Weeks</SPAN>
31/07/2012</SPAN>	2 Weeks</SPAN>
31/07/2012</SPAN>	2 Weeks</SPAN>
01/12/2011</SPAN>
01/12/2011</SPAN>
28/11/2011</SPAN>	This is what a get - all incorrect

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[Jonmo1]

The formula is doing exactly what you told it to do..
It asks if the date in CK8 is Greater than a date 2 weeks ago.
Those date's that show 2 Weeks that you just posted, are all in the future.
So a date in the future, IS in fact greater than a date 2 weeks ago.

Perhaps you want
If CK8 is > 2weeksago AND < Today

Try
=IF(AND(CK8>=TODAY()-(2*7),CK8<=TODAY()),"2 Weeks","")

 

[bobcunnie]

That works - thank you so much

 

[Jonmo1]

Glad to help, thanks for the feedback.