Hi guys, can you help me please?
Let me explain it a bit better... i hope
In Cell A2 I have a Deadline Month e.g May-11. Now depending on what the current month is I need to enter a value in cell B2. The paramaters are:
1st: If the Deadline Date is <=6 months from the Current Month enter in cell B3 "Deadline <=6"
2nd If the Deadline Date is greater than 6 months BUT less than 12 months enter in "Deadline >6<12"
3rd: If the Deadline Date is greater than 12 months enter in "Deadline >12"
I can't seem to work out the 2nd parameter - between 6 and 12 months.
Your help would be greatly appreciated once again
Mike
Last edited by zeppelinmike; 11-11-2011 at 05:34 AM.
If you want it to show like your example
Try
=CHOOSE(SIGN(B2-A2)+2,"WD", "Competed", "OD")
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Try this:
=IF(MONTH(A2)=MONTH(B2),"Completed",IF(MONTH(B2)-MONTH(A2)<=6,"WD","BD"))
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
If you can stand another option, here it is. I didn't use your statuses (for reasons you'll see below) but I am showing status that should be very clear what they mean.
=LOOKUP(YEAR(B1)*12+MONTH(B1)-YEAR(A1)*12-MONTH(A1),{-99,0,1,6,12,13},{"Early","On Time","<=6","<=12",">12"})
However, I must note that your description does not sync with the example you listed. Your status does not distinguish between >6 and >12, and I'm not sure what you mean by Completed. Once it is completed, do you always change the deadline to match the finish date? And do I understand correctly that something done 6 months late is still considered "within deadline"?
Last edited by 6StringJazzer; 11-10-2011 at 06:13 PM. Reason: Screwed up and left test cells in my formula, corrected with blue text
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
This will not work if the months are in two different years.Originally Posted by alansidman
Re: IF Function- Deadline dates and Status
Let me explain it a bit better... i hope
In Cell A2 I have a Deadline Month e.g May-11. Now depending on what the current month is I need to enter a value in cell B2. The paramaters are:
1st: If the Deadline Date is <=6 months from the Current Month enter in cell B3 "Deadline <=6"
2nd If the Deadline Date is greater than 6 months BUT less than 12 months enter in "Deadline >6<12"
3rd: If the Deadline Date is greater than 12 months enter in "Deadline >12"
I can't seem to work out the 2nd parameter - between 6 and 12 months.
Your help would be greatly appreciated once again
Mike
Last edited by zeppelinmike; 11-11-2011 at 05:34 AM.
Try using DATEDIF, i.e.
=IF(A2="","","Deadline "&LOOKUP(DATEDIF(A2,TODAY(),"m"),{0,6,12;"<=6",">6<12",">12"}))
Audere est facere
Works a treat Daddylonglegs! Now could I add an OR into this formula. For example if cell B3 has a actual completion date within it - i want cell B2 to just state "Completed'
Your help on this greatly appreciated by an nofice Excel user!
If A2 has the deadline month, B2 is either blank or has a completion date then formula in C2 can be as follows:
=IF(A2="","",IF(B2<>"","Completed","Deadline "&LOOKUP(DATEDIF(A2,TODAY(),"m"),{0,6,12;"<=6",">6<12",">12"})))
Audere est facere
Thanks very much! That worked great! Much appreciated daddylonglegs!
Hi Daddylonglegs, sorry to bother you again but I forgot to consider future dates i.e. future project deadline dates not reached yet. For example, if cell A2 has a deadline date greater than the current month - I would like the Status cell (C2) to input "Within Deadline".
When I tested this using your formula I received the #N/A! error.
I'm gradually getting to understand how IF formulas are created - with your help you've done for me so far, thanks again"
Hi Daddylonglegs, sorry to bother you again but I forgot to consider future dates i.e. future project deadline dates not reached yet. For example, if cell A2 has a deadline date greater than the current month - I would like the Status cell (C2) to input "Within Deadline".
When I tested this using your formula I received the #N/A! error.
I'm gradually getting to understand how IF formulas are created - with your help you've done for me so far, thanks again"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks