

April 23, 2015

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

Power Query
Power Pivot
Power BI

October 17, 2018


VIP

Trusted Members

December 7, 2016

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.
1 Guest(s)
