Log In
Register
Home
April 23, 2015
Offline
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
Offline
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")
VIP
Trusted Members
December 7, 2016
Offline
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)
