Hello,
I am trying to write an IF formula that returns values if the adjacent cell falls into a date range that is measured by the day number that year. For example, If the number is within 20 days of day 45 (less than day 45+10 (55) and greater than day 45-10 (35). I run into a problem when the range falls over the new year, day 365 and day 1. So I have the formula somewhat correct, just not sure on how to do the math for when the range falls over the new year. I imagine it is adding an if greater than 365 or less than 0 then add 365 etc. But can't seems to get it.
I have attached a sample sheet. Here are the columns:
A) The day number of the year represented by the formula --> =B3-DATE(YEAR(B3),1,1)+1
B) The date
C) Values A
D) Values B
E) The formula that only shows/repeats the value of values B/column D if the IF function is true
You will see the variable numbers highlighted yellow. If you change the day number and the range so it flows over the new year, day 1 or 365, All of the numbers disappear.
I really hope I am explaining this correctly.
Thanks so much!
Bookmarks