Help with formula

[Richard]

=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5) This
formula works good except for one final thing! Since B16 is the actual start
date, and less than 90 days = 0 days accured, but 90 days = accural rate of
0.0274 up untill January 1 the following year, then it's 10 days. Here is my
problem... I can't figure out how to use the accural rate just untill January
1st rolls around then stopping accural time and changing results to 10.
Thanks so much! It is above and beyond me!!!

 

[Allllen]

Richard,

This should be easy to solve but you are going to have to make it clear
under what circumstances you want to return the value 10. That was not very
clear from your message.

Try to think about it like this and get it completely separate in your
brain, before you start writing the formula:

1) IF the gap from today to the end of the year is less than 90 days THEN
take the value 0.

2) Otherwise, IF (under what circumstances???) THEN the value should be 10.

3) Otherwise, IF the gap from today to the end of the year is > 90 days THEN
multiply the number of days remaining this year by 0.0274 and add 0.5.

The nested IF formula will look something like this:
=IF(TODAY()-B16<90,0,IF([YOUR NEW
CONDITION],10,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5))

HTH

 

[Allllen]

Richard,

Sorry I totally confused myself as well. I meant

1) IF the gap from B16 to today less than 90 days THEN take the value 0.

2) Otherwise, IF (under what circumstances???) THEN the value should be 10.

3) Otherwise, multiply the number of days between B16 and the end of the
year by 0.0274 and add 0.5.

The nested IF formula will look something like this:
=IF(TODAY()-B16<90,0,IF([YOUR NEW
CONDITION],10,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5))

HTH
--
Allllen

--
Allllen

Richard,

This should be easy to solve but you are going to have to make it clear
under what circumstances you want to return the value 10. That was not very
clear from your message.

Try to think about it like this and get it completely separate in your
brain, before you start writing the formula:

1) IF the gap from today to the end of the year is less than 90 days THEN
take the value 0.

2) Otherwise, IF (under what circumstances???) THEN the value should be 10.

3) Otherwise, IF the gap from today to the end of the year is > 90 days THEN
multiply the number of days remaining this year by 0.0274 and add 0.5.

The nested IF formula will look something like this:
=IF(TODAY()-B16<90,0,IF([YOUR NEW
CONDITION],10,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5))

HTH
--
Allllen

=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5) This
formula works good except for one final thing! Since B16 is the actual start
date, and less than 90 days = 0 days accured, but 90 days = accural rate of
0.0274 up untill January 1 the following year, then it's 10 days. Here is my
problem... I can't figure out how to use the accural rate just untill January
1st rolls around then stopping accural time and changing results to 10.
Thanks so much! It is above and beyond me!!!

 

[Richard]

Richard,

This should be easy to solve but you are going to have to make it clear
under what circumstances you want to return the value 10. That was not very
clear from your message.

Try to think about it like this and get it completely separate in your
brain, before you start writing the formula:

1) IF the gap from today to the end of the year is less than 90 days THEN
take the value 0.

2) Otherwise, IF (under what circumstances???) THEN the value should be 10.

3) Otherwise, IF the gap from today to the end of the year is > 90 days THEN
multiply the number of days remaining this year by 0.0274 and add 0.5.

The nested IF formula will look something like this:
=IF(TODAY()-B16<90,0,IF([YOUR NEW
CONDITION],10,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5))

HTH
--
Allllen

=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5) This
formula works good except for one final thing! Since B16 is the actual start
date, and less than 90 days = 0 days accured, but 90 days = accural rate of
0.0274 up untill January 1 the following year, then it's 10 days. Here is my
problem... I can't figure out how to use the accural rate just untill January
1st rolls around then stopping accural time and changing results to 10.
Thanks so much! It is above and beyond me!!!

 

[Richard]

Up untill January 1st. then 10

Richard,

This should be easy to solve but you are going to have to make it clear
under what circumstances you want to return the value 10. That was not very
clear from your message.

Try to think about it like this and get it completely separate in your
brain, before you start writing the formula:

1) IF the gap from today to the end of the year is less than 90 days THEN
take the value 0.

2) Otherwise, IF (under what circumstances???) THEN the value should be 10.

3) Otherwise, IF the gap from today to the end of the year is > 90 days THEN
multiply the number of days remaining this year by 0.0274 and add 0.5.

The nested IF formula will look something like this:
=IF(TODAY()-B16<90,0,IF([YOUR NEW
CONDITION],10,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5))

HTH
--
Allllen

=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5) This
formula works good except for one final thing! Since B16 is the actual start
date, and less than 90 days = 0 days accured, but 90 days = accural rate of
0.0274 up untill January 1 the following year, then it's 10 days. Here is my
problem... I can't figure out how to use the accural rate just untill January
1st rolls around then stopping accural time and changing results to 10.
Thanks so much! It is above and beyond me!!!