Formula for start data and end dates to match year

[ahmadhassan]

I have sheet with this data

No., S_date, E_date, Monthly_rent, (years) and total

I need when write S_date and E_date, sum of each year is calculated separately. How can do it?

Thanks for support me

[jeffreybrown]

If I understand correctly, try in B2 copied down and to the right...

=IF(AND(E$1>=YEAR($B2),E$1<=YEAR($C2)),$D2,"")

[ahmadhassan]

If I understand correctly, try in B2 copied down and to the right...

=IF(AND(E$1>=YEAR($B2),E$1<=YEAR($C2)),$D2,"")

Thanks for help me but is wrong

The S_date is 01/04/2016 and E_date 15/06/2023 and monthly rent is 200$, So yearly is for:

2016 = "1800", 2017 = "2400", 2018 = "2400", 2019 = "2400", 2020 = "2400", 2021 = "2400", 2022 = "2400" and 2023 = "1200"

[jeffreybrown]

Thanks for help me but is wrong

I would respectively disagree with this statement.

I did say if I understood correctly, so what would help, please update your worksheet with what you expect for the entire table. E2:N4.

[ahmadhassan]

I would respectively disagree with this statement.

I did say if I understood correctly, so what would help, please update your worksheet with what you expect for the entire table. E2:N4.

Updated sheet with data

[maras_mak]

Test my attachment.
Best Regards,
Maras.

[ahmadhassan]

Test my attachment.
Best Regards,
Maras.

Thanks Maras that is required, also I need explain if S_date from 1 to 15 of month also E_date from 16 to last month equal half month
And if you have time and fast explain formula to learn.
Thanks again

[maras_mak]

In the attachment, my corrected formulas according to your needs.
Best Regards,
Maras.

[AliGW]

Sorry for off-topic interjection:

@Maras Mak

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

[davsth]

=AND(YEAR($B4)<=E$1,YEAR($C4)>=E$1)*$D4*(12-IF(AND(YEAR($B4)=E$1,DAY($B4)>15),MONTH($B4)-0.5,IF(YEAR($B4)=E$1,MONTH($B4)-1,0))-IF(AND(YEAR($C4)=E$1,DAY($C4)<=15),13-MONTH($C4)-0.5,IF(YEAR($C4)=E$1,12-MONTH($C4),0)))

firstly is the year in the range of rental contract AND(YEAR($B4)<=E$1,YEAR($C4)>=E$1) if this is not the case everything is 0

rent is monthly rental * months


if we start at 12 months, we need to remove months from this figure if the rental starts partway through the year
IF(AND(YEAR($B4)=E$1,DAY($B4)>15),MONTH($B4)-0.5,IF(YEAR($B4)=E$1,MONTH($B4)-1,0))

if the start date was for example 15/4/16 we would take away MONTH($B4)-1 months = 3 months (Jan- mar)

if the start date was for example 15/4/16 we would take away MONTH($B4)-0.5 months 3.3 months (Jan- to half april)


Similar has to happen at the end of the rental
if the end date was for example 15/6/23 we would take away 13-MONTH($C4)-0.5 months 6.5 months (half june-Dec)
if the end date was for example 16/6/23 we would take away 12-MONTH($C4) months 6 months ( july-Dec)

if the year does not have the start or the end of the contract in it it is just 12 * monthly rental with no subtractions

[ahmadhassan]

In the attachment, my corrected formulas according to your needs.
Best Regards,
Maras.

Yes Maras, I need formula calc such as example results

[ahmadhassan]

=AND(YEAR($B4)<=E$1,YEAR($C4)>=E$1)*$D4*(12-IF(AND(YEAR($B4)=E$1,DAY($B4)>15),MONTH($B4)-0.5,IF(YEAR($B4)=E$1,MONTH($B4)-1,0))-IF(AND(YEAR($C4)=E$1,DAY($C4)<=15),13-MONTH($C4)-0.5,IF(YEAR($C4)=E$1,12-MONTH($C4),0)))

Thanks Davsth,
Can explain me, if I need edit on your formula and calc by date of month such
S_date is 20/4/2014 and E_date in 16/01/2023 the result is in Jan 2023 is 533.33 "If monthly rent 1000$"

[davsth]

its a little unclear, your example of halfs was confusing earlier inthe thread

=AND(YEAR($B2)<=E$1,YEAR($C2)>=E$1)*$D2*(12-IF(AND(YEAR($B2)=E$1,DAY($B2)>15),MONTH($B2)-0.5,IF(YEAR($B2)=E$1,MONTH($B2)-1,0))-IF(AND(YEAR($C2)=E$1,DAY($C2)<=15),13-MONTH($C2)-0.5,IF(YEAR($C2)=E$1,12-MONTH($C2),0)))

first month proportion
1-(DAY($B2)-1)/DAY(EOMONTH($B2,0))

Last month proportion
=(DAY($C2))/DAY(EOMONTH($C2,0))

Jan isn't 533 as there are 31 days in January

and 20/4 is the last 11 days in April

[ahmadhassan]

first month proportion
1-(DAY($B2)-1)/DAY(EOMONTH($B2,0))

Last month proportion
=(DAY($C2))/DAY(EOMONTH($C2,0))

Jan isn't 533 as there are 31 days in January

and 20/4 is the last 11 days in April

Thanks Davsth and I apologize for confusing, but can you tell me an example of a complete formula, or where this part is changed in the previous formula

1-(DAY($B2)-1)/DAY(EOMONTH($B2,0))

Last month proportion
=(DAY($C2))/DAY(EOMONTH($C2,0))

[davsth]

the proportion of the month just gets multiplied by the month, so both formulas as multiplied by 2
1-(DAY($B2)-1)/DAY(EOMONTH($B2,0))*d2

Last month proportion
=(DAY($C2))/DAY(EOMONTH($C2,0))*d2

[ahmadhassan]

the proportion of the month just gets multiplied by the month

Thanks Bro,
Exactly this formulas works with me, but in the first and last cell only.

But how to put this formulas (First & Last month )

=(DAY($B2)-1)/DAY(EOMONTH($B2,0))*d2

=(DAY($C2))/DAY(EOMONTH($C2,0))*d2

within full formula in other cell to work on all cells.

I put final result on attached

[davsth]

Perhaps
=(if(and(year($b2)<=e$1,year($c2)>=e$1),12,0)-if(year($b2)=e$1,month($b2)-1+(day($b2)-1)/day(eomonth($b2,0)),0))*$d2

[ahmadhassan]

Perhaps
=(if(and(year($b2)<=e$1,year($c2)>=e$1),12,0)-if(year($b2)=e$1,month($b2)-1+(day($b2)-1)/day(eomonth($b2,0)),0))*$d2

Work on S_date but in E_date not work

[davsth]

=(IF(AND(YEAR($B2)<=E$1,YEAR($C2)>=E$1),12,0)-IF(YEAR($B2)=E$1,MONTH($B2)-1+(DAY($B2)-1)/DAY(EOMONTH($B2,0)),0)-IF(YEAR($C2)=E$1,13-MONTH($C2)-(DAY($C2))/DAY(EOMONTH($C2,0)),0))*$D2

Any better

[ahmadhassan]

=(IF(AND(YEAR($B2)<=E$1,YEAR($C2)>=E$1),12,0)-IF(YEAR($B2)=E$1,MONTH($B2)-1+(DAY($B2)-1)/DAY(EOMONTH($B2,0)),0)-IF(YEAR($C2)=E$1,13-MONTH($C2)-(DAY($C2))/DAY(EOMONTH($C2,0)),0))*$D2

Any better

Many thanks bro Davsth

[davsth]

To try and explain if a start year is equal to or less than the year, and the end year is greater than or equal to the end year, then you charge rent. If the year in question is in the middle this is easy as the rent is 12 * D2 (monthly rent)
If the year is the start or end year (or both) this becomes a little more complicated

at the start we would take away the number of months -1 from the 12, so if it was an April start we would remove 3 months, we would also need to remove the proportion of the month that had already happened. so if it was the 10th of April as the start
(DAY($B2)-1)/DAY(EOMONTH($B2,0))



we would wish to remove 9/30ths (10-1)/days in April

A similar thing needs to take place at the end of the period

We need to remove the months with no rent
13-MONTH($C2) so if the end was 20 th october, we would remove 3 months

we then would need to add on the days in the month rent could be charged
DAY($C2))/DAY(EOMONTH($C2,0)

=20/31

so in a rental from 10th April to 20 October

we would charge (12- ( 3+ (9/30))-(3-20/31)=6.34161 months rent

Any clearer