This is the formula to calculate if assessments have happened, going to
happened or hasn't happened

=IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS
YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED
OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",)))

Every time I change the figure to test the function the response is
"ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out.

Where am I going wrong?

You always get that, because the first part of your formula always gives true
in other words
= x = 0 < 365 returns true for all values of x

if you don't believe me, just go and type
= 1 = 0 < 365 in a cell
then type
= NOW()-AF2=0<365 and you will see that it is always true

And of course 0 < 365. Always.
--
Allllen


"Pasty" wrote:

This is the formula to calculate if assessments have happened, going to
happened or hasn't happened

=IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS
YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED
OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",)))

Every time I change the figure to test the function the response is
"ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out.

Where am I going wrong?

Yeah I noticed that shortly after I wrote this - I'm trying to get the first
bit to say if between 0 and 365 (can be equal to both of these values as
well) but I'm having a bit of difficulty.

"Allllen" wrote:

You always get that, because the first part of your formula always gives true
in other words
= x = 0 < 365 returns true for all values of x

if you don't believe me, just go and type
= 1 = 0 < 365 in a cell
then type
= NOW()-AF2=0<365 and you will see that it is always true

And of course 0 < 365. Always.
--
Allllen


"Pasty" wrote:

This is the formula to calculate if assessments have happened, going to
happened or hasn't happened

=IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS
YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED
OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",)))

Every time I change the figure to test the function the response is
"ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out.

Where am I going wrong?

try
=IF(and(NOW()-AF2=0,NOW()-AF2<=0365),"ASSESSMENT CARRIED OUT THIS
YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED
OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",)))


"Pasty" wrote:

Yeah I noticed that shortly after I wrote this - I'm trying to get the first
bit to say if between 0 and 365 (can be equal to both of these values as
well) but I'm having a bit of difficulty.

"Allllen" wrote:

You always get that, because the first part of your formula always gives true
in other words
= x = 0 < 365 returns true for all values of x

if you don't believe me, just go and type
= 1 = 0 < 365 in a cell
then type
= NOW()-AF2=0<365 and you will see that it is always true

And of course 0 < 365. Always.
--
Allllen


"Pasty" wrote:

This is the formula to calculate if assessments have happened, going to
happened or hasn't happened

=IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS
YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED
OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",)))

Every time I change the figure to test the function the response is
"ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out.

Where am I going wrong?

Try something like this:

With
A2: (a date)

B2:
=LOOKUP(TODAY()-A2,{-100000,0,366},{"ASSESSMENT PLANNED FOR THIS YEAR BUT
NOT CARRIED OUT","ASSESSMENT CARRIED OUT THIS YEAR","ASSESSMENT NOT PLANNED
OR FAILED TO HAPPEN"})

or

Build this table on another worksheet or in a blank range on the current
sheet.
(I'll assume it's on the current sheet in cells Y1:Z3
-100000 ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT
0 ASSESSMENT CARRIED OUT THIS YEAR
366 ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN

Then
B2: =LOOKUP(TODAY()-A2,$Y$1:$Y$3,$Z$1:$Z$3)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"Pasty" wrote:

This is the formula to calculate if assessments have happened, going to
happened or hasn't happened

=IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS
YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED
OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",)))

Every time I change the figure to test the function the response is
"ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out.

Where am I going wrong?