Previous Topic Next Topic

Excel calculations on dates

Closed
egeuens - Mar 16, 2011 at 03:03 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 17, 2011 at 09:58 AM
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

Answer 1 / 3
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 16, 2011 at 07:26 AM
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.
0
Answer 2 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 17, 2011 at 08:37 AM
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
0
Answer 3 / 3
egeuens Posts 1 Registration date Thursday March 17, 2011 Status Member Last seen March 17, 2011
Mar 17, 2011 at 09:45 AM
Hi,

Indeed I need the number of days per year, grouped.
Any idea how to realize it?
Thx
Erwin
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 17, 2011 at 09:58 AM
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
0

Similar discussions

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

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

1 response
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses