If Function and dates

[mattyblueice]

I have a sheet where I want to flag 60, 90 and 120 days reviews based on an effective date and Calendar dates, but the formula I have is not working. Here is what I have:

[TABLE="width: 900"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Customer[/TD]
[TD]Effective Date[/TD]
[TD]11/1/2017[/TD]
[TD]12/1/2017[/TD]
[TD]1/1/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B. Smith[/TD]
[TD]9/1/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is the formula I have in D2 and copied over to E2:F2

=IF(($C2+60)=11/1/17,"60 Day Review",IF(($C2+90)=11/1/17,"90 Day Review",IF(($C2+120)="11/1/17","120 Day Review","")))

D2 should say 60 Day Review, E2 should say 90 Day review and F2 should say 120 Day Review, but it does not - they are all blank.

Any ideas on how to get this to work?

Thanks!

Matt

 

[Jonmo1]

The formula will read not read 11/1/17 as a date
Instead it reads it like 11 divided by 1 divided by 17
Try like this

=IF(($C2+60)="11/1/17"+0
or better
=IF(($C2+60)=DATE(2017,11,1)

And I'm guessing you're really looking at 'ranges' of dates.
So you might want to use >= instead of just =

 

[mattyblueice]

Thanks that go the bulk of them by changing the "11/1/17" to Date(2017,11,1) and using >= but its still picking up dates less than 60 and oddly enough future dated effective dates, i.e. 1/1/2018. So probably need to tweak it a bit so that the dates its reading are more precise?

 

[Jonmo1]

Can you post the formula you have now? And give a more detailed explanation of what you expect it to do. It's not exactly clear to me what ranges you expect to be 60 or 90 etc..

 

[mattyblueice]

Sure - so here is the formula I have now from a random cell: [FONT=Calibri, sans-serif]=IF(($E4+60)>=DATE(2017,11,1),"60 Day Review",IF(($E4+90)>=DATE(2017,11,1),"90 Day Review",IF(($E4+120)>=DATE(2017,11,1),"120 Day Review","")))[/FONT]

[FONT=Calibri, sans-serif]E4 = Effective Date[/FONT]

[FONT=Calibri, sans-serif]This issue I have now is that for an effective date os 10/1/2017, under 11/1/17 the result is "60 day Review" when only 30 days have passed and for a effective date of 1/1/2018, its also showing a "60 Day Review" for some weird reason.[/FONT]

 

[Jonmo1]

This issue I have now is that for an effective date os 10/1/2017, under 11/1/17 the result is "60 day Review"

Your first IF is True
IF(($E4+60)>=DATE(2017,11,1),"60 Day Review",
If E4+60 is Greater than or Equal to Nov 1st 2017 then return "60 Day Review"
So if E4 is 10/1/2017 Then
10/1/2017 + 60 = 11/30/2017
11/30/2017 is in fact Greater Than 11/1/2017
So the first IF is TRUE and it returns the "60 Day Review"

 

[Jonmo1]

I still don't understand how your ranges are defined.

 

[Jonmo1]

I can only offer explanation of why your latest formula did what it did.
I can't really offer solution or better idea because I still don't understand what you want the formula to do.

Forget the formula for a minute. And look at the table you originally posted

[TABLE="width: 900"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Customer
[/TD]
[TD]Effective Date
[/TD]
[TD]11/1/2017
[/TD]
[TD]12/1/2017
[/TD]
[TD]1/1/2018
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B. Smith
[/TD]
[TD]9/1/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In your head, what is the logic followed
What condition(s) would cause D2 be "60 day review" or "90 day review" or "120 day review"
Same question for E2 and F2

 

[mattyblueice]

The formula is working in the spreadsheet for 9/1/2017. In another row I have the effective date as 10/1/17 and under Column D its showing 60 day review when only 30 days have passed. I also have an effective date of 1/1/2018, its also showing a "60 Day Review" - that's the [FONT=Calibri, sans-serif]problem I am having with it[/FONT]

 

[Jonmo1]

In another row I have the effective date as 10/1/17 and under Column D its showing 60 day review when only 30 days have passed.

What DO you want it to show 'Instead' of the incorrect "60 day review" ?
Do you want "30 day review" ?
Well your formula doesn't include a criteria for 30 days.
Maybe you just need to add that criteria..

=IF($E4+30>=DATE(2017,11,1),"30 Day Review",IF($E4+60>=DATE(2017,11,1),"60 Day Review",IF($E4+90>=DATE(2017,11,1),"90 Day Review",IF($E4+120>=DATE(2017,11,1),"120 Day Review",""))))