Excel "IF" question

=DateY - min(DateA, DateB)

u...........da man

its not working with the MM/DD/YYYY format.

I did

=DAYS360(MIN(A,B),Y) since I am trying to find the difference in MONTHS between the earlier of A and B .

Therefore, if A is earlier then B, I want:

A-Y

If B is earlier then A, I want :

B-Y

IN MONTHS is the KEY

aHH and i divided all by thirty

May I ask why you're using the days360 function? If you're using data that is spread over over several years, you're going to see fairly significant deviations from the days360 function (which is based on a 360 day year) and just subtracting the dates (which will give the actual number of days in between). I think DrJoe's method is more accurate.

I will assume your dates are stored as dates, and not as text, etc. Check this by adding 1 to a date - it should show give you the next date.

If you want the difference in months, then it gets a bit tricky due to year ends. I think the following would work though.

The difference in months between X and Y is "=month(y) - month(x) + 12*(year(y) - year(x))"

Since you want x to actually be the minimum of A and B, replace X everywhere it appears with min(a,b). You might have to reverse the sign of the result, since in above posts you have been inconsistent about what you are subtracting from what.

Dr Joe

yeah i need to do min(a,b) - y

So let's do this, sorry but I'm new to excel.

A= 03/05/1980 B=04/05/1980 Z= 08/08/1993

I would need to do A-Z (A is March, B is April of 1980)

So what would my function be?

i need months because it's for a bond that pays monthly cash flows

DrJoes works for this.

"=MONTH(8/8/1993) - MONTH(MIN(3/5/1980,4/5/1980) + 12*(year(8/8/1993) - year(3/5/1980,4/5/1980))"

Should be 9+144+8=161.

But obviously you'll be using this more than once so don't hardcode the dates, link them to the cells you'll be using and paste.

BUT...

I don't believe the above formula will be exact if DAY(x) greater than DAY(y). For the above you would get 161 even though technically it wouldn't be a full month from day 5 of 3/1980 to say day 20 of 8/1993 which would only be 160 full months.

Then you would need an IF clause. Where the above formula is F -

"=IF(DAY(y) greater than Day(x),F,F-1)"

Right?

Ok, tested it. This works to take care of the day issue.

J15=MIN(A,B) J21=Z

"=IF(DAY(J21)>DAY(J15),MONTH(J21)-MONTH(J15)+12(YEAR(J21)-YEAR(J15)),MONTH(J21)-MONTH(J15)+12(YEAR(J21)-YEAR(J15))-1)"

Another way could be to use YEARFRAC function.

Set up 4 columns. 1st column (C1): YEARFRAC(Min(A,B),Y) = 13.425 2nd column (C2): =YEAR(Z)-YEAR(MIN(A,B))-IF(MONTH(MIN(A,B))>MONTH(Z),1,0) = 13 {OP this is saying year 1993 - 1980, and if the minimum's month is after Z's month, you need to take away 1 to account for less than a year}

3rd column (C3): Round((C1-C2)*12,0) {This gives you # of months)

4th column = C2 x 12 months + C3 = 13*12 + 5 months = 161 months.

I did it in steps to show the OP the formula breakdown in an easier format, but I think the other guys' formulas in one step works too.

it's a actually a fixed income derivative. the question is relatively simple.

I have a column that says Adjustable Rate, and the cells are all "Y," or "N"

If it's adjustable, I need to calculate in months, the remaining term. Which I know is classified as, by my bank,

Earlier of NDate or MDate minus Ldate. The obvious trick here is the months, and If function.

So, for a different classification, where I had to calculate the fixed term as

ldate-mdate, i did

=IF(Cell1"N",((DAYS360(CellLdate,Cellmdate)/30),0

oh shit for the ARM i forgot to mention allota the N dates are blank, so you need to do a IF>0 as well

figured that part out, but oh crap, it gets harder.......

Figured out that question.......

QUICK excel question.

I have cells with months. 1 - 100 i believe

if it's less then three months, i need the cell to read "3m" 3 months-12 months "12M" 1 year to 3 years "3M" 3-5 years, 5y 5 yrs to 15 years, 15y, 15+ over 15.

some sort of if function.................

Two things you can do - Match function or vlookups

To figure out match function, use the Excel help, it is pretty good on the topic. I prefer doing this using a distinct reference table. So create a new sheet and list out the possible numbers of months - 1 to 100, as you said. List each of them. Next to each, enter the description that corresponds to it - so next to 0,1,2 enter 3m, next to 3,4,5,.... 12 enter 12m and so forth. Then use a vlookup on the original tab.