Numbers - can't override its automatic format

Hi tellesent,

You wrote:

" It was supposed to calculate the number of days between a series of cells formatted as 'Dates', and then multiply the results (which it automatically formatted as 'days') by the number of pills per day in the period to give a simple total in the Results cells. But it has decided to change the format of the Results cells from numeric to give the answer as 'Duration' in weeks and days."

The result makes sense.

If you multiply the number of, say, cows, by a numerical value, the result is a number of cows.

If the result of the initial calculation was n days, then multiplying that number of days by a number would similarly result in a number of days—a duration, rather than a unitless number. Numbers reverted to formatting this result as a duration because that is what it was. Numbers hasn't "changed" the format from numeric; it has refused to accept that format as one that could be applied to a duration value. A duration can't be formatted as a number. It can be converted to a number, and that number will accept formatting as a number.

I suspect you simply subtracted the start date from the end date.

The difference between two dates is a duration, not a unitless number.

The DATEDIF function will give you the difference between two dates as a number, representing the number of day, the number of months, the number of years, or representing three other available choices.

Here's an example showing two sets of calculations. Both sets use the dates in A2 and A3 of the table, and multiply the result of the difference calculation by the number in the 'pills/d' row.

The formula in the unlabeled row is shown in the cell at the top of the column containing that formula:

Regards,

Barry

Hi telessent,

You can convert a duration (in days) to a number with the DUR2DAYS function.

Regards,

Ian