Forum Discussion
Nested referenced date addition formula
Hello everyone. I've a new problem I can't seem to find answers for and I'm hoping someone can help.
Cell A61 has a formula which returns the date "Tue 02-27" (2018 although the year not shown)
In cell M68, I'm trying to create a formula that will add 2 days to it, then compare JUST the day, and if true, then return the whole NEW date. Something along the lines of the following...
=IF((DAY(A61)+2)="1",(A61+2),"Not")
I've tried many things for many hours but can't get anything to work properly.
(DAY(A61+2) should return 1 (Feb 27 + 2 days is Thursday March 01, so 1 for the day). Therefore...
IF((DAY(A61)+2)="1" Should be true, so it should return "Thu 03-01". However, it returns 3 instead, turning it to the date of Tue 01-03 (for the year 1900).
I hope I was able to explain it clear enough and also hope someone is able to help.
Thank you
There are many reasons to have that result.
You may use array formula in a function that it does not support. or using relative reference in correctly, or miss to handle some cases in if statement.
Your A61 is a generated value. There are many reasons. Also, you did not check the formula that I pointed out that is is not correct.
Day(A1)+2 <> Day(A1 + 2)
- Can you double check the formula? Is it really
=IF((DAY(A61)+2)="1",(A61+2),"Not")
or
you wrote it to
=IF((DAY(A61)+2)="1",DAY(A61+2),"Not")My formula returns the whole NEW date, your version would just return the new DAY.
the correct formula is
=IF(DAY(A61+2)=1,(A61+2),"Not")
I can't assume the formula is correct to find the problem
- No, the formula was correct. Assuming the formula actually worked, my way would take the date in A61, (Tue 02-27), working with only the day (27), add 2 to it, (making the new date, Thu 03-01, but is still working with only the day, (01)), then compare the day 01 to 1 and if it's equal, woul return the new full date (Thu 01-01).
