Training Update formula

[tim677]

I'm still struggling with this formula

I have the following
=IF(V9+365>P3,AB1,AB2)
V9 = the cell a date is typed in
P3 is using =NOW() function
AB1 = 1
AB2 = 2
This sheet will be used to identify if training requires updating annually (1) meaning in date and (2) meaning out of date.
I may need to re-write this formula but I need
1 = greater than 0 but less than 335
2= greater than 335 but less than 365
3 = greater than 365

 

[jasonb75]

Try

=MATCH($P$3-V9,{0,335,365})


edit:- Duplicate thread http://www.mrexcel.com/forum/showthread.php?t=535297

 

[Giordano Bruno]

Does this help?

=IF(V9+334>P3,AB1,IF(V9+364>P3,AB2,AB3))

I suggest that P3 should use the TODAY function so that the results won't change during the day.

 

[Dryver14]

A different view


=IF((P3-V9)<1,3,IF((P3-V9)<32,2,1))

 

[tim677]

Thank you all for your responses. The point of using the =NOW() function is for the results to change every day, allow me to elaborate:-
I have produced a training matrix which managers use to identify staff training updates.
They type in the date e.g 24/03/2011 and this feeds of the =NOW() cell so when they open the spreadsheet on 24/03/2012 it will automatically alert them that training it required. This is indicated with ticks and crosses (using conditional formating) I now need to give them a months warning that the training will soon need to require an update using an exclamation mark.

 

[Dryver14]

all giordano was saying was the now function has minutes and second whilst today() just has the date.
I am a little lost now,

we have all given you different formulas to achieve a 1 2 or three, is this not what you are looking for after all?

 

[jasonb75]

Comparing this to the duplicate thread, which has slightly different criteria, maybe

=LOOKUP(TODAY()-V9,{0,335,365},{1,3,2})

 

[tim677]

Right you are, I didn't consider the conditional formatting I had on at the time.
Thank you so much, this has been driving me mad.