Hi everyone,
I have a simple formula that calculates the number of days from "today" to a date entered on the worksheet, it adds the text "days" to make it a little more meaningful for the user. However when the date cell is blank is displays a large negative number e.g. -43621. I thought I could get around that by adding an IF statement so I changed the formula as below:
Original formula: =M2-TODAY()& " days"
Amended formula: =IF(M2-TODAY()& " days"<0,"",M2-TODAY()& " days")
But of course its not working, the function arguments help displays this as "volatile". I did a quick search online and think this is because of the "today" variable in the formula - is that the problem? Is there anyway to preserve the original formula but amend it such that a negative count of days will not be displayed?
Alan
I'm going to give this a shot, so take with a grain of salt. I personally don't mind the negative days, but maybe you could try an if statement using isblank?
=IF(ISBLANK(M2),"Cell needs a date",M2-TODAY()& " days")
Hello,
More info here about the TODAY function.
Change your formula to =IF(M2-TODAY()<0,””,M2-TODAY()&” days”) and it will work. You can also use custom number format to not displaying the negative numbers.
Steve/Anders,
Thank you both very much. Steve for some reason I was not able to get your formula to work but that could just be me!
Anders, your formula worked, thank you.
