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

#### mgmtspt

##### New Member
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/2013 03/08/2014 L

<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.

Last edited:

#### Pup Denab

##### Active Member
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

##### Well-known Member

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

#### shg

##### MrExcel MVP

Or,

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

#### mgmtspt

##### New Member
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

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

#### Pup Denab

##### Active Member
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
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.

Replies
13
Views
180
Replies
7
Views
224
Replies
4
Views
184
Replies
1
Views
540
Replies
7
Views
211

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.

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.

### Which adblocker are you using?

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

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