Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

# Question:Q:Numbers - can't override its automatic format

I'm fairly new to Numbers having been brought up on Lotus and Excel.

I have Numbers v3.6.2 running under El Capitan 10.11.6 on a Macair.

I've created a small spreadsheet to keep track of pills usage. 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.

Whatever I do I can't change the format - the stoooopid thing just automatically reverts.

Anyone got any ideas why it would do this and how to overcome it - or do I need to abandon Numbers and go back to Excel?

iPhone 4s (8GB), iOS 9.3.5, With macair late 2014 osx10.11.6

Posted on

Hi telessent,

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

Regards,

Ian

Mar 10, 2018 6:28 PM

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

Mar 10, 2018 10:09 PM

User profile for user: telessent

Question: Numbers - can't override its automatic format