Calculating with dates

[Bizzybee]

I would like to do the following:

Example data:
I have a lease entered into on 10 November 2004 and which finishes on 10 May 2006. Monthly rental is $100.

There are lots of leases all at different times/years etc.

How can I calculate how much was due per year, i.e. in 2004, 2005, 2006?

I am essentially looking for a formula that will calculate the months in 2004, recognise that 2005 is a full year and find the right part of 2006 and apply the monthly rental.

Can anyone help?

Thank you!!!

 

[AlanY]

try this

Excel 2012
	A	B
1	Start	10-Nov-04
2	End	10-May-06
3		100
4
5	2003
6	2004	100
7	2005	1200
8	2006	500
9	2007
Sheet1

Cell Formulas
Range	Formula
B5	=IFERROR($B$3*IF(A5=YEAR($B$1),DATEDIF($B$1,"31/12/"&A5,"m"),IF(A5=YEAR($B$2),DATEDIF("1/1/"&A5,$B$2,"m")+1,IF(AND(A5>YEAR($B$1),A5$B$2)),12,""))),"")

 

[Bizzybee]

Thank you so much.

I have to say I can't quite follow the formula, the DATEIF function is new on me and I struggle with so many IF functions lined up within each other.

But it works, so many thanks for that.

 

[AlanY]

you're welcome

the datedif(...,"m") function is to work out the months between the 2 dates.
and the if's etc are to compare the year in A5 to A8 etc with the start and end dates

 

[mayflower09]

Hi, I have a separate question but same general topic. Trying to add dates depending on if meets criteria. If the date is less than 7 days then I want the function to go to the next Thursday before. If it is more than 7 days, then it can leave on that Thursday date.

Here's the equation I have: =IF((L3-WEEKDAY(L3+3)-6)<8,L3-14,IF((L3-WEEKDAY(L3+3)-6)>8,N3))

However, it gives back "1/0/1900" so don't know how to fix. My L3 equation is- =IF(OR(WEEKDAY(K3)=1,WEEKDAY(K3)=7),K3-WEEKDAY(K3+1),K3)

And lastly for K3 I put an equation to add 30 days from the stamp date that I manually type in J3.

 

[63falcondude]

Hi, I have a separate question but same general topic. Trying to add dates depending on if meets criteria. If the date is less than 7 days then I want the function to go to the next Thursday before. If it is more than 7 days, then it can leave on that Thursday date.

Here's the equation I have: =IF((L3-WEEKDAY(L3+3)-6)<8,L3-14,IF((L3-WEEKDAY(L3+3)-6)>8,N3))

However, it gives back "1/0/1900" so don't know how to fix. My L3 equation is- =IF(OR(WEEKDAY(K3)=1,WEEKDAY(K3)=7),K3-WEEKDAY(K3+1),K3)

And lastly for K3 I put an equation to add 30 days from the stamp date that I manually type in J3.

Hello and welcome to the forum.

FYI, you should have started a new thread for your question but since this is here already, we can work with it here.

1/0/1900 changed to Number format is 0.

Please create a small sample of your data along with the desired output of the formula. This is much more helpful for us than a non-working formula.

 

[mayflower09]

Thank you for your fast response and apologies for not making a new post! Basically date received is date I put in, goal date is just the cell#+30. Meeting potential I want to do 1 week before the goal date, but if it falls on a Thursday then I want to do it the week before that date. So for example 6/1 falls on a Thursday so I want to push back to 5/25.

Here are my equations: =IF(OR(WEEKDAY(K2)=1,WEEKDAY(K2)=7),K2-WEEKDAY(K2+1),K2) for meeting potential
Meeting date- =IF((L3-WEEKDAY(L3+3)-6)<8,L3-14,IF((L3-WEEKDAY(L3+3)-6)>8,N3))

L3 is goal date

Here is the data:
[TABLE="width: 496"]
<tbody>[TR]
[TD][/TD]
[TD]date received [/TD]
[TD]Goal Date [/TD]
[TD]Meeting Potential [/TD]
[TD]Meeting Date [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/9/2017[/TD]
[TD]6/8/2017[/TD]
[TD]6/1/2017[/TD]
[TD]5/25/2017[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]