Today function in a If statement

[Phillipo12]

Hi everyone,

Im trying to create a tracker that displays the current grade of my class to the main dashboard along with the progression number which is determined from the predicted grade to the current grade. say they were predicted an A and the current grade was a B the progress would be -1.

Anyways the function I have so far is:

=IF(TODAY()>(1/10/15),'Predicted grades Maths'!H3,IF(TODAY()>(1/11/15),'Predicted grades Maths'!J3,IF(TODAY()>(1/12/15),'Predicted grades Maths'!L3,IF(TODAY()>(1/1/16),'Predicted grades Maths'!N3,IF(TODAY()>(1/2/16),'Predicted grades Maths'!P3,IF(TODAY()>(1/3/16),'Predicted grades Maths'!R3,IF(TODAY()>(1/4/16),'Predicted grades Maths'!T3,IF(TODAY()>(1/5/16),'Predicted grades Maths'!V3,IF(TODAY()>(1/6/16),'Predicted grades Maths'!X3,"false")))))))))

But I am having trouble. I want the tracker to update to the next cell after the date has passed. I am aware I am using greater than but I have tried with less than too.

Is there a way of looking at a range of dates and if false move on to the next nested IF?



Thank you for your help.


Phil

 

[Jonmo1]

It's these parts

TODAY()>(1/10/15)
That is reading not seeing 1/10/15 as a Date.
Instead it reads it as 1 divided by 10 divided by 15.

Try changing those to
TODAY()>DATE(2015,10,1)

 

[Special-K99]

You need to put the highest date first, ie put the dates in reverse order.
If TODAY() > 1/6/16 should result in 'Predicted grades Maths'!X3
but since it's also > 1/10/15 the result will be 'Predicted grades Maths'!H3, the first IF condition, which will be the wrong result.

Reverse the IF conditions so the highest date/result is first.

 

[Phillipo12]

You need to put the highest date first, ie put the dates in reverse order.
If TODAY() > 1/6/16 should result in 'Predicted grades Maths'!X3
but since it's also > 1/10/15 the result will be 'Predicted grades Maths'!H3, the first IF condition, which will be the wrong result.

Reverse the IF conditions so the highest date/result is first.

Ah of course it's so simple once someone says it! I've been hitting my head off a wall for hours!! Thank you!!!!!!

 

[Phillipo12]

Just got a chance to amend it. Its still doesn't work. I have reversed the logic and it now just displays false.

=IF(TODAY()>(1/6/16),'Predicted grades Maths'!X3,IF(TODAY()>(1/5/16),'Predicted grades Maths'!V3,IF(TODAY()>(1/4/16),'Predicted grades Maths'!T3,IF(TODAY()>(1/3/16),'Predicted grades Maths'!R3,IF(TODAY()>(1/2/16),'Predicted grades Maths'!P3,IF(TODAY()>(1/1/16),'Predicted grades Maths'!N3,IF(TODAY()>(1/12/15),'Predicted grades Maths'!L3,IF(TODAY()>(1/11/15),'Predicted grades Maths'!J3,IF(TODAY()>(1/10/15),'Predicted grades Maths'!H3,"false")))))))))

I have test it and it doesn't seem to be the false that is set to the false option in the IF it is just an error.

I also tried to replace the date format to use commas but it didn't seem to like that and threw up a error message.

 

[Phillipo12]

It's these parts

TODAY()>(1/10/15)
That is reading not seeing 1/10/15 as a Date.
Instead it reads it as 1 divided by 10 divided by 15.

Try changing those to
TODAY()>DATE(2015,10,1)

Sorry is the format yyyy,mm,dd

 

[holystoner]

You can make a table that has your dates in it and link your formula to those cells.... i.e If(Today()>Sheet2!$A$1, ......

I like this solution better because then all your dates are linked to cells that can be changed as desired without going into the formula.

 

[Jonmo1]

Yes

 

[Mackers]

Try this:

=IF(TODAY()>DATE(2016,6,1),X3,OFFSET(X3,0,-(1+DATEDIF(TODAY(),DATE(2016,6,1),"m"))*2))

Thanks

Mackers

 

[Phillipo12]

You can make a table that has your dates in it and link your formula to those cells.... i.e If(Today()>Sheet2!$A$1, ......

I like this solution better because then all your dates are linked to cells that can be changed as desired without going into the formula.

This worked perfectly- I will test properly tomorrow. Simple and easy thanks!!