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

mgmtspt

New Member
Joined
Nov 1, 2013
Messages
3
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".

Start Date (A1)End Date (B1)S or L (C1)
10/09/201303/08/2014L

<tbody>
</tbody>

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.
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Excel 2010
ABC
1Start Date (A1)End Date (B1)S or L (C1)
210/9/20133/8/2014S
310/9/20134/8/2014L
410/9/20132/8/2014S
Extract # of months
Cell Formulas
RangeFormula
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
NameRefers ToCells
o='Convert Amount to words'!$I$5:$AB$5
 
Last edited:
Upvote 0
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"))
 
Upvote 0
Or,

=if(datedif(a1, b1, "m") <= 5, "S", "L")
 
Upvote 0
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.


ABC
1Start Date (A1)End Date (B1)S or L (C1)
22/1/20146/30/2014S (Correct)
32/1/20147/1/2014S (This should be "L")
42/1/20147/2/2014L (Correct)

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
2/1 to 7/1 is exactly five months; why shouldn't it return "S"?
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top