I need to improve a formula that distinguishes between <=5 months, and > 5 months.

[mgmtspt]

Users enter two dates. The formula needs to determine the number of months beginning with the first date and ending with the second. 5 months or less should return a "S". Greater than 5 months should return a "L".

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date (A1)[/TD]
[TD]End Date (B1)[/TD]
[TD]S or L (C1)[/TD]
[/TR]
[TR]
[TD]10/09/2013[/TD]
[TD]03/08/2014[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]

C1 Formula:

=IF((YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)>=5,"L",IF((B1)-(A1)>=1,"S","0"))

The problem is the above dates represent 5 months so it should result in a S, but it's showing a L.

 

[shyy]

Excel 2010
	A	B	C
1	Start Date (A1)	End Date (B1)	S or L (C1)
2	10/9/2013	3/8/2014	S
3	10/9/2013	4/8/2014	L
4	10/9/2013	2/8/2014	S
Extract # of months

Cell Formulas
Range	Formula
C2	=IF(ROUND((B2-A2)/30,)<=5,"S","L")
C3	=IF(ROUNDUP((B3-A3)/30,)<=5,"S","L")
C4	=IF(ROUNDUP((B4-A4)/30,)<=5,"S","L")

Named Ranges
Name	Refers To	Cells
o	='Convert Amount to words'!$I$5:$AB$5

 

[Pup Denab]

Hi mgmtstp
Welcome to the forum, dates are tricky, I asume this is Month Day Year format, so try the below (the 30.42 is the average lenght of a month)


=IF(B1-A1>5*30.42,"S",IF(B1="","0","L"))

 

[bschwartz]

How about:

=VLOOKUP((B1-A1)/30,{0,"S";6,"L"},2)

 

[shg]

Or,

=if(datedif(a1, b1, "m") <= 5, "S", "L")

 

[mgmtspt]

Hi Shyy:

Of all the solutions offered I think(?) yours comes closest.

In particular:

=IF(ROUNDUP((B1-A1)/30,)<=5,"S","L")

The reason this works best for me is because if the total is 5 months and 1 day, I need to return a "L".

However, for the shortest month set (by my reckoning), I still get a wrong answer for one date set(*). That is, with the one exception, I believe your solution works for all other possible date sets.

	A	B	C

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Start Date (A1)[/TD]
[TD="bgcolor: #FAFAFA"]End Date (B1)[/TD]
[TD="bgcolor: #FAFAFA"]S or L (C1)[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]6/30/2014[/TD]
[TD="bgcolor: #FAFAFA"]S (Correct)[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]7/1/2014[/TD]
[TD="bgcolor: #FAFAFA"]S (This should be "L")[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]7/2/2014[/TD]
[TD="bgcolor: #FAFAFA"]L (Correct)[/TD]

</tbody>

​

 

[shg]

5 months or less should return a "S". Greater than 5 months should return a "L".

if the total is 5 months and 1 day, I need to return a "L".

I can't reconcile those two statements.

 

[shg]

2/1 to 7/1 is exactly five months; why shouldn't it return "S"?

 

[Pup Denab]

Hi mgmtstp
Welcome to the forum, dates are tricky, I asume this is Month Day Year format, so try the below (the 30.42 is the average lenght of a month)


=IF(B1-A1>5*30.42,"S",IF(B1="","0","L"))

Lets try that one again, I have had some time to reconsider this, the following formula will work this out correctly, however you may need to adjust depending on whether you want exactly 5 months or just under 5 months
if just under 5 months, i.e. a day less, use 0.413

=IF(YEARFRAC(A1,B1)>0.415,"L","S")

 

[mgmtspt]

Thanks for the follow-up Pup Denab. I failed to mention I'm still using v2003 so no YEARFRAC option, but I'm supposed to get an upgrade to 2010 soon and will test your solution then. In the interim, Shyy's formula above will suffice.

Thanks again everyone for the assist. I appreciate it.