Function: check cell value is>= date

[itsm3json]

Hi everyone,

I've tried to get this formula to work however I am not having any luck.


Basically what I am trying to do 2 things:

1. if B1 <= 6 after the 15th of each month then B3 = $0. No bonus

2. if cell B1 is >=7 by or before the 15th of each month. then B3 = $250. Bonus given
and since 7 was hit by/before the 15th, the rest of the month B3 should = $250


my issue is once B1 is >6, B3 will show $250 bonus. but since the fast start
was not hit by the 15th of the month no bonus should be given.


any help would be greatly appreciated.

[ChemistB]

The problem is that Excel doesn't have a memory of what was in that cell before. So if B1 becomes 7 before the 15th of the month, but later drops to 6, Excel only sees the 6. Also if it's after the 15th of the month, it doesn't remember if B1 was 7 before the 15th, it only knows what it is today.

A few comments about your formula
=IF(AND(B1<=6,DATE(2013,3,15)),"0",IF(AND(B1>=7,TODAY()<=DATE(2013,3,15)),"250",250))

1. Don't want the quotes around 0 and 250 (unless you want it treated as text)
2. Your first Date statement isn't compared to anything. I think you want DATE(2013,3,15)<= TODAY()
3. You have if B1>=7 and TOday's date is less than 3/15 set B3 to 250, if it's false, set it to 250.

I think you will need to resort to VBA to actually get your spreadsheet to work the way you want it to.

[etaf]

you need to have a field to put the date >6 is reached

otherwise i dont see how you can tell if 7 was achieved before 15th of month
you can test for today being greater than 15th - But if 7 is achieved anytime between 15 and 31st - it will be TRUE

so you can test <=6 and no bonus

how is this going to be used , are you opening the test on the 15th and making the call then ?