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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Start Date (A1)</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">End Date (B1)</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">S or L (C1)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;color: #333333;;">10/9/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;color: #333333;;">3/8/2014</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">S</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;;">10/9/2013</td><td style="text-align: right;border-right: 1px solid black;;">4/8/2014</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">L</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;;">10/9/2013</td><td style="text-align: right;border-right: 1px solid black;color: #333333;;">2/8/2014</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">S</td></tr></tbody></table><p style="width:11.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Extract # of months</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">ROUND(<font color="Red">(<font color="Green">B2-A2</font>)/30,</font>)<=5,"S","L"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=IF(<font color="Blue">ROUNDUP(<font color="Red">(<font color="Green">B3-A3</font>)/30,</font>)<=5,"S","L"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=IF(<font color="Blue">ROUNDUP(<font color="Red">(<font color="Green">B4-A4</font>)/30,</font>)<=5,"S","L"</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">o</th><td style="text-align:left">='Convert Amount to words'!$I$5:$AB$5</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Pup Denab

Active Member
Joined
May 12, 2013
Messages
299
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"))
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Or,

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

mgmtspt

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows
2/1 to 7/1 is exactly five months; why shouldn't it return "S"?
 

Pup Denab

Active Member
Joined
May 12, 2013
Messages
299
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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,262
Messages
5,769,105
Members
425,518
Latest member
seothaeng

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
Top