Some background information regarding Date and Time values in Numbers, followed by another solution to your question.
In Numbers, a Date & Time value always includes a Date part and a Time part.
If you enter only a 'time of day', Numbers automatically sets the Date part to the date on which the entry was made.
If you enter only the Date part, Numbers automatically sets the Time part to 00:00:00 (Midnight, at the beginning of that day).
In these two cases, the default format shows only the part ( date or time) that has been entered, but the actual value in the cell where this was entered includes both the entered part and the other part, calculated by the application.
The result of subtracting one Date & Time value from another is a Duration, whose units are indicated by a one or two letter label automatically applied to the result (example: the d in your result.
Fortunately, Numbers also supports a set of DURto… functions that will convert a Duration in Weeks, Days, Hours, Minutes, Seconds or milliseconds to a Number, to which you can append a word label matching the unit in which the Duration value has been expressed by the number.
Example:
The formula shown below the table is entered as shown in cell B2, then filled down to cell B5. Numbers automatically increments the row of 'A2' to match the row containing that copy of the formula.
On rows containing a Date (and time) value, the formula calculates the duration between that date and 'today', converts the result to a number representing that duration as a number (of days), then appends a space and the word "Days" to that number, as shown in column B.
In Row 5, DURATION interprets the empty cell in column A as containing a numerical value of 0, tries to subtract that value from the Date and Time value 'TODAY', and throws the error message seen in the example below:
The same situation occurs in row 6, but here the formula has been revised to recognize that error, abandon the calculation, and place a null string in the cell. The null string is a text value with a length of zero, making its cell appear to be empty, as seen in B6 of the first image.
With the whole formula placed in a single cell, there is no need for a 'reference cell'
Regards,
Barry
