Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Logical function
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. |
#2
|
|||
|
|||
Quote:
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Logical function
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 |
#4
|
|||
|
|||
Quote:
It worked... |
#6
|
|||
|
|||
Quote:
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 |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
Quote:
=IF(X2="","",IF(X2=15,TEXT(M2,"MMM"),IF(X2<15,TEXT(EOMONTH(M2,-1),"MMM")))) |
#9
|
|||
|
|||
Here's the example workbook, that is similar to the one i am working with.
|
#10
|
|||
|
|||
Quote:
=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. |
#11
|
|||
|
|||
Yippe, Thanks, well i was trying with the formula from 2 days and messed it up... Only shifting and 3M's directed it to the condition.... Thank you spencer... Thank you very very much...
|
#12
|
|||
|
|||
Not a problem Rajesh. Always happy to help. :)
|
#13
|
|||
|
|||
Back again with a question with excel formula, The earlier formula was used to create a pivot table, which now is successfully created, but from that pivot table i want to create a comprehensive table which gives total of workload per person irrespective of team, i tried to create an another pivot table from the first one but one cell in header is blank, it is not feasible. So i decided to list down the names of person in table 2, but the problem i cannot figure out feasible way to get an automatic update to return me the sums of above also the header should be repeated with out the team/group name.
Enclosing herewith an example sheet... I know this might be frustrating but please do help.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Logical IF function | Excel Worksheet Functions | |||
Logical Function excel 2003 | Excel Worksheet Functions | |||
Help!!! - logical function for someone not very logical | Excel Worksheet Functions | |||
EXCEL 2000 - IF function returns wrong result from logical test | Excel Worksheet Functions | |||
Help with logical function | New Users to Excel |