# help required for calulating days and dates

#### egeuens

##### New Member

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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Airfix9

##### Well-known Member
We know that leap years contain 366 days and other days only 365 and that, handily, leap years are always a multiple of 4 so:

=MOD(YEAR(A1),4)

... will return a 0 for all leap years.

#### WaterGypsy

##### Well-known Member

=MIN(C3,\$B4)-MAX(C2,\$A4)+1

Where A4 is the start date, B4 is the end date, then in c2 you have 1st of the year and c3 has the last day in the year.....

... this should also work where you have any start and end date for the period you want to count days for.

#### WaterGypsy

##### Well-known Member
btw - there is a mistake in the formula but I bet you find it easily enough

#### Weaver

##### Well-known Member
Building on WaterGypsy's solution

In C1 - H1 put your years (2006 - 2011) this is purely for labelling
In C2 - H2 put the 1st of Jan for each year
In C3 - H3 put the 31st dec for each year
in A4 goes the start date and B4 has the end date

Then in C4, the formula:

=MIN(C\$3,\$B4)-MAX(C\$2,\$A4)+1

You can then copy this across to column H and down for however many rows you need

HTH

#### WaterGypsy

##### Well-known Member
In case I'm offline later I thought I better put the correction in - I forgot to check that the period was in the year at all

=IF(OR(\$B4 < C2,\$A4 > C3),0,MIN(C3,\$B4)-MAX(C2,\$A4)+1)

#### Weaver

##### Well-known Member
or

=MAX(MIN(C\$3,\$B4)-MAX(C\$2,\$A4)+1,0)

#### egeuens

##### New Member
Thank you for the suggestions but I probably explained the case not correctly (EN is not my native language)

I have a list of 100 lines. Each line has a start date between 2006 and 2011) and each line has a end date. Each line contains also a basic price.

The interest rate is different each year. SInce I need to calculate per day between the start and end date the interest to invoice it is pretty complicated.
My sheet contains:
intr intr int int
line price start end 2006 2007 2008 2009
line 1 500 € 1/4/2006 1/9/2009 5.50% 3.25% 2.75% 3.25%

so in line 1 we have (31/12/2006-1/4/2006) days for 2006
a full year for 2007, idem for 2008
and a number of days in 2009 (enddate - 1/1/2009)

after the calculation I can then use a standard forumla for the interests per year.

#### Weaver

##### Well-known Member
I think if you follow my methodology to begin with, you ought to be able to insert lines and/or columns where required and amend the formulas to multiply the number of days in each cell by whatever you need.

Replies
1
Views
697
Replies
1
Views
581
Replies
9
Views
1K
Replies
6
Views
770
Replies
3
Views
979

1,171,650
Messages
5,876,664
Members
433,205
Latest member
jabin1991

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back