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

Numbers automatically selects "date" format for cells.

Numbers automatically selects "date" format for certain cells.

I need "numbers".

I want to calculate simple interests due as a function of amount, interest rate and number of days.

When I input "number" format for the result cell and calculate amount*interest rate*number of days divided by 365 it always automatically shows something unwanted and formatted as "date". Moreover I am not able to change the automatic format ("date") into "number". How to get around this automatic selection?


Thanks in advance


UbiUbu

MacBook, iOS 9.2.1

Posted on Feb 11, 2016 4:46 AM

Reply
5 replies

Feb 11, 2016 6:09 AM in response to Wayne Contello

Hello Wayne,


the problem appears when the number of days in not a "number" but instead is a calculated result of deduction of two "date" cells - (date actual - date due) and shows in a cell as a "number of days" format (eg. 25d). Otherwise there is no problem.

It looks like the existence of a "number of days" or "date" cells in a calculated format alway (at least in my case) forces the automatic selection of a result format as "date' or "number of days".



principal

date due

date paid

number days

interests per year

interests per day

interests due


30000

30.06.2015

04.03.2016

248d

2 400

6,58

232t 6d 16h 26m 18s 82ms


65000

30.07.2015

04.03.2016

218d

5 200

14,25

3 105,75


The problem is in the first row - the formula is principal*8%*(date paid-date due)/365

By the way I also tried to format the number days column as a "number" - does not allow me - the automatic format always overwrites to "duration"


Well, if you could help I'd appreciate. I am a new user of numbers. Many years in Excell. I love the new system but sometimes it is a pain.


Ubi.

Feb 11, 2016 6:51 AM in response to ubi-ubu

Hi Ubi.


Numbers handles dates differently from Excel. When you subtract them you get a "duration" value, as you see in your 'number days' column. Numbers has a function called DUR2DAYS to convert a duration to a number of days that can be used in non-date calculations.


So you could try principal * 8% * DUR2DAYS(date paid - date due) / 365.


SG

Numbers automatically selects "date" format for cells.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.