help required for calulating days and dates

egeuens · Mar 16, 2011

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

Airfix9 · Mar 16, 2011

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.

Does that help you to get going?

WaterGypsy · Mar 16, 2011

How about :

=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 · Mar 16, 2011

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

Weaver · Mar 16, 2011

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 · Mar 16, 2011

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 · Mar 16, 2011

or

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

egeuens · Mar 16, 2011

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 · Mar 16, 2011

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.

help required for calulating days and dates

egeuens

New Member

Excel Facts

Airfix9

Well-known Member

WaterGypsy

Well-known Member

WaterGypsy

Well-known Member

Weaver

Well-known Member

WaterGypsy

Well-known Member

Weaver

Well-known Member

egeuens

New Member

Weaver

Well-known Member

Similar threads

Forum statistics

Share this page

help required for calulating days and dates

New Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock