Cell formatting

Question

Level 1

8 points

Cell formatting

Hi everybody,

I'm trying to calculate interest using the following table:

From	To	Days	Rate	Principle	Interest
11.1.2016	31.1.2016	20d	5	3000	8d 8h

The problem is that I can't change the format of the cell in col: Interest from days/hours format to number format. I have the same issue in Pages (5.6.1) and in Numbers (3.6.1). Col: Days contains the following formula: "b2-a1" and the cell automatically converts the result in date format. When I try to calculate the interest using "Days*Rate*Principle/36000" the result of 8d 8h appears and this format cannot be changed to 8,33 as I expect to see.

Posted on May 5, 2016 8:22 AM

Reply

Answer 1

Best reply

VikingOSX

Community+ 2024

Level 10

111,323 points

May 5, 2016 9:27 AM in response to tfrombs

I set your Rate field to percentage, the Principal field to currency with two decimal points, and the Interest field to currency with four decimals. The same cell formats and formulas work in Pages v5.6.1, and Numbers v3.6.1. Tested on OS X 10.11.4.

The result:

The Days cell:

The Interest cell:

Reply

Answer 2

tfrombs Author

Level 1

8 points

May 5, 2016 9:35 AM in response to VikingOSX

Thanks Viking,

I tried your approach and it worked when I changed the format of the column "Principle" into currency format. However, such a formula works perfectly on Excel and I have no idea why Apple did it so difficult/bothering on their iWork suit.

Reply

Answer 3

May 5, 2016 11:37 AM in response to tfrombs

There is no Microsoft Excel code in Numbers. It is Apple's interpretation of a spreadsheet, and there is no guarantee of 100% interoperability between Excel spreadsheets and Numbers.

Reply

Answer 4

SGIII

Level 8

35,276 points

May 5, 2016 4:06 PM in response to tfrombs

tfrombs wrote:

However, such a formula works perfectly on Excel and I have no idea why Apple did it so difficult/bothering on their iWork suit.

Numbers and Excel work with dates differently. The Numbers approach can be very convenient. Note that in C you automatically have a "duration" Data Format because you've subtracted two dates. If you convert that to decimal with the DUR2DAYS function then your other formulas should work as you are accustomed without having to change the data format of other cells (though here VikingOSX's solution is more elegant). For more on other duration functions in Numbers/Pages go to Help > Formulas and Functions Help in your menu and search on DUR. (Excel has a DURATION function too but it means something completely different; it has to do with bond calculations.)

SG

Reply