formula with dates (months) in a year

[excelquestions1]

Hi,

I have a large list of start and end dates that vary. I need to look up and calculate the number of months in a Fiscal Year period (7/1/11-6/30/12). For example, start-end dates of 7/31/11-8/31/11 should be 1 month. Whereas 7/1/11-7/31/16 should be 12 (maximum for a 12 month fiscal year.) Or the start date could be 4/1/12-6/30/13, which would be 3 months (through 6/30/12). In other words, I can't include months prior to July 2011 or after June 2012.
Also, I would like to calculate half-months i.e. 7/15/11-9/30/11, should be 2.5 months total.
I've started off with (YEAR(B130)-YEAR(A130))*12+MONTH(B130)-MONTH(A130)
and also
DATEDIF(A130,B130,"m")
but am stuck on how to use start/end dates as parameters.

Thanks.

 

[barry houdini]

Hello and welcome to MrExcel

Perhaps try using YEARFRAC function like this

=IF(COUNT(A130,B130)=2,YEARFRAC(MAX(DATE(2011,7,1),A130),MIN(DATE(2012,7,1),130))*12,"")

 

[excelquestions1]

I'll try that. I was just getting a decent result with this
=DATEDIF(A259,MIN(B259+1,$P$1+1),"M")
(where A is 'start date' column, B is 'end date' column and P is a cell with 6/30/2012 (end date for Fiscal Year.))

but I'll try yours now and see if better result...will report back shortly.
Thanks.

 

[excelquestions1]

I see the fraction result, but not sure it's what I need?
This is from row 2147...note, I think you left out the letter A in last part of your formula above before '130'? I put it before last 2147 below.

IF(COUNT(A2147,B2147)=2,YEARFRAC(MAX(DATE(2011,7,1),A2147),MIN(DATE(2011,7,1),A2147))*12,"")

My result is for '4' months. But the start date (11/1/11) and the end date (10/31/12) should make the answer 8 months (i.e. # of months between and including Nov11-June12).

 

[barry houdini]

Well DATEDIF will only give you whole months with "m"...

My version should have B at the end, not A, i.e.

=IF(COUNT(A130,B130)=2,YEARFRAC(MAX(DATE(2011,7,1),A130),MIN(DATE(2012,7,1),B130))*12,"")

 

[excelquestions1]

Ok, revised it with a B at end.
But still not giving result i.e. 11/1/2011 start date and 10/30/2012 ending date should be maxed up to 6/30/12 (or 8 months total) but the result with your formula displays 4 months? Not sure why.

I just need my formula to count 0-12 months total, with a period only including between 7/1/11-6/30/12 (regardless of whether 'start' or 'end' dates are before or after those dates.

Basically, if I have a 6 month, 1 year, 2 year or 5 year contract, I just want to see the portion to impact just the Fiscal Year (7/1/11-6/30/12). If it can round up/down or include decimals, great. If not, that's okay.)
Thanks so much! Very helpful so far.

 

[barry houdini]

Ok, revised it with a B at end.
But still not giving result i.e. 11/1/2011 start date and 10/30/2012 ending date should be maxed up to 6/30/12 (or 8 months total) but the result with your formula displays 4 months? Not sure why.

Can you try again - I get 8 with those dates......

 

[excelquestions1]

Ah...my mistake, I had 2011 repeated twice in formula. I changed last part to 2012 and it works for that line/cell. I'll re-check the formulas later/tomorrow (got to head home now) and report back if any issues.

Thanks!

 

[excelquestions1]

Slight change although your prior formula worked.

I need to use start dates (columnA) and end dates (column B) but my data in column F is already broken out by month. So for example, if I had $1200 for 12 months, it's broken out $100/mo....if it is 36 month contract, I need to only show the $1,200 (100 x 12).

So I just want a simple if/then statement (?) that says if start date is >6/30/2011 and <7/1/2012, show the data in column F (otherwise, blank.)

I started with this but am stuck...

=(IF(A3>DATE(2011,6,30),B3<DATE(2012,7,1),F3))

Thanks