0

I have a formula where I am comparing the number of days between two dates, however not all rows have one of the dates in which event Excel tries to calculate the difference between 01/01/1900 and the date e.g. a negative 41xxx number.

I want my formula to leave the cell blank when this happens and since my column type is whole numbers using "" will not work for me. Using 0 will not work either as it will affect my data as there will be some legitimate rows where 0 is the correct result.

How can I return a NULL so these rows are simply left empty?

Example formula below:

=IF([dateColA] = "", NULL, [dateColB]-[dateColA])

A small note, I am actually using Power BI Desktop however the engine accepts virtually all of Excel's formulas.

James
  • 737
  • 2
  • 11
  • 27
  • Possible duplicate of [Output a NULL cell value in Excel](http://stackoverflow.com/questions/2558216/output-a-null-cell-value-in-excel) – zaptask Mar 02 '16 at 10:29
  • 1
    What do you want to do with the data finally? Can you allow error values? In such a case you could return a function NA() for example. – zaptask Mar 02 '16 at 10:43

1 Answers1

2

While the linked question and its "official parent question" are good reading material for Excel use-cases, a better answer for PowerBI is the BLANK() function (in DAX), and its cousin ISBLANK(). Reference forum, blog post, and MSDN (apparently the content hasn't been moved to docs yet).

So your formula would likely look something like this:

IF(ISBLANK([dateColA]), BLANK(), [dateColB]-[dateColA])

If this helps and is better suited for your case, you should probably edit the question and change the tag to powerbi. =)

NateJ
  • 1,935
  • 1
  • 25
  • 34