I am trying use the nested IF function to do the following steps:
To make clear in Cell M2, I would have the project deadline date in mm/dd/yyyy format.
I want the following condition to be satisfied.
1. IF date is greater than or equal to 15 than the same month should be in Y2
2. IF date is less than 15 than the earlier month should be returned back in Y2
3. If the M2 is blank than it should return a blank Cell in Y2

So this seems a multiple if condition, got bit confusing so what i did is used
=IF(M20,DAY(M2), "") which returns the date from reference cell.
and used
=IF(X2=15,TEXT(M2,"MMM"), IF(X2<15,EOMONTH(M2,-1), IF(X2="","",""))) to return the all the above condition, all works fine but when X2 is blank, then it returns JAN as month instead of blank cell can somebody help.

Put the IF(X2="","" section at the start of the formula and all should work out.

Thank you for your help,

It worked...

Not a problem! Glad to help :)

Could you please help me with one more thing..

As per the conditions provided, The EOMONTH(M2,-1) returns me a numerical value, i have formatted these cells to display it as 'mmm', so it shows me as Apr when the deadline date is 05/03/2012 (mm/dd/yyyy). But when i created a pivot table to check the workload per person in an individual month these cells were not considered under the same month but displayed as date separately, which is invalid date, So in pivot table formatted the cells to get MMM, but they are as separate columns.... Could you please help me

To help with that you will have to post an example workbook so we can have a look at where it's going wrong and help fix it.

Here's the example workbook, that is similar to the one i am working with.

Try putting the below formula in F2 on Sheet1 and copying down.

=IF(E2="","",IF(E2=15,TEXT(D2,"MMM"),TEXT(EOMONTH (D2,-1),"MMM")))

Then refresh the pivot table and I THINK that does what you need.

Does this solve it?

=IF(X2="","",IF(X2=15,TEXT(M2,"MMM"),IF(X2<15,TEXT(EOMONTH(M2,-1),"MMM"))))

Rajesh Bhapkar used his keyboard to write :
I am trying use the nested IF function to do the following steps:
To make clear in Cell M2, I would have the project deadline date in
mm/dd/yyyy format.
I want the following condition to be satisfied.
1. IF date is greater than or equal to 15 than the same month should be
in Y2
2. IF date is less than 15 than the earlier month should be returned
back in Y2
3. If the M2 is blank than it should return a blank Cell in Y2

So this seems a multiple if condition, got bit confusing so what i did
is used
=IF(M20,DAY(M2), "") which returns the date from reference cell.
and used
=IF(X2=15,TEXT(M2,"MMM"), IF(X2<15,EOMONTH(M2,-1), IF(X2="","",""))) to
return the all the above condition, all works fine but when X2 is blank,
then it returns JAN as month instead of blank cell can somebody help.

Try this:

=IF(X2="","",IF(X2=15,TEXT(M2,"MMM"),IF(X2<15,EOM ONTH(M2,-1))))

Bruno