Previous Topic Next Topic

Excel calculations on dates

Closed
egeuens - 16 Mar 2011 à 03:03
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 17 Mar 2011 à 09:58
Hello,


I have following:
a start date, an end date. Difference can be 1 day up to 10 years.

I need to calcuate the numbers of days representing each year.
ex: start = 1/4/2006, end = 1/6/2011
how many days in 2006, 2007, 2008, 2009, 2010, 2011?

This is needed because I need to calculate interests per each day and the interest rate is different each year.
ex: interest in 2006 = 5.50, in 2007 = 3.25 ... and I need to have a total sum of all interest the customer need to pay after the period.

I already tried datedif() but this is not enough. I can calculate the number of days of the first year but that is it.

Since this calculation will be used on Mac and PC (both version of Excel >= 2003) I can not use macro's or visual basic.
It has to be in normal formula's.

Thk you very much for each suggestion.

Erwin
Related:

3 responses

Réponse 1 / 3
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
16 Mar 2011 à 07:26
it is better to put a column w.r.t. each year and than use datedif,

otherwise you have to use lots of if statement.
Réponse 2 / 3
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 Mar 2011 à 08:37
what you mean by days between days. Do you mean to seek number of days between two dates (in which case datediff would have worked with parameter "d")

If you mean to have number of days between two days but grouped separetely for each year, then it becomes bit more complicated
Réponse 3 / 3
egeuens Posts 1 Registration date Thursday 17 March 2011 Status Member Last seen 17 March 2011
17 Mar 2011 à 09:45
Hi,

Indeed I need the number of days per year, grouped.
Any idea how to realize it?
Thx
Erwin
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 Mar 2011 à 09:58
Since you say that at the end of all this, what you care is have a total interest payable between days. Taking into account for each year interest rate is different, the best solution in my opinion would be that you write a custom function. In a sheet or may be even in the function you can put the interest values for the year and let that custom function get the days for each and add up all the amount to give you the final answer

Similar discussions

encryption message several time in chat
measking -
HelpiOS -

1 response
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses