Evovling Date Return

[Baz_Barrett1970]

First off, I have used this site as reference extensively throughout my Military Service and have produced spreadsheets that are now used as 'best practice' in over 80 countries wordwide - thanks to the knowledge I have gleaned from you guys - so thanks for your support. This is my first ever thread...

My dilema is a date return. I have formulated a competency framework that when a competency is achieved, a date is entered from a drop down menu and a traffic light system monitors the competency for a specific and defined period (green in date, amber one the before expiry and red when expired).

I am trying to apply the same principle for Staff appraisals bearing mind each rank has a different appraisal date. The problem is I want to return a date that is quantifiable as opposed to one that is a text entry. Whilst at first glance this may appear simple, the main stumbling block is that the date retun has to be absolute (dd/mmm) yet evolving in years (as opposed to simply 12 months after the competency was achieved); meaning, the return cannot be a specific year (12) as the appraisal is due on the same month every year.

currently the return formula is:

=IF(C6="RANK1","NOV",IF(C6="RANK2","NOV",IF(C6="RANK3","MAY",IF(C6="RANK4","SEP",IF(C6="RANK5","JAN",IF(C6="RANK6","28 FEB",IF(C6="RANK7","N/A",)))))))

The formula's used for the traffic light system are:

=IF(G6=""," ",DATE(YEAR(K6), MONTH(K6)+12, DAY(K6)))
=DAYS360(H6,$W$4). Cell $W$4 is a =now() formula.

I hope I have explained this sufficiently for one of you fine persons to assist?

Thanks in advance

 

[jasonb75]

Welcome to the board!

I'm not sure if I'm following what you need correctly, revising your current return formula to

=IFERROR(DATEVALUE(CHOOSE(--RIGHT(C6),"1/11","1/11","1/5","1/9","1/1","28/02")&"/"&YEAR($W$4)),"N/A")

Would return a date as the 1st day of the revelant month in the current year (taken from the date in $W$4), Rank6 is set to 28 Feb as with your original formula.

Assuming that there is something in the sheet to identify when the appraisal is carried out, a logical test could be used to verify that and add a year to the date.

For exampe, entering "yes" in D6 when the appraisal is carried out, would change the dates to 2013.

=IFERROR(DATEVALUE(CHOOSE(--RIGHT(C6),"1/11","1/11","1/5","1/9","1/1","28/02")&"/"&(YEAR($W$4)+(D6="yes"))),"N/A")

The result could then be compared to the current date for your traffic light evaluation.

Hope this helps.

 

[Baz_Barrett1970]

That makes absolute sense Jason - thanks. I will give it a whirl to see what occurs.. I may not reply with the results for a few days however as I am going on 'holiday' again in the morning.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
The return "28 Feb" was just me messing around with a definitive date as opposed to a generic month - 'experimenting out loud' so to speak.<o></o>
<o> </o>
I have tried to overcome my dilemma by the end-user selecting and inputting the yyyy part of a date formula - from a drop down menu (the dd/mm/ part of the formula being set by me (dependant on rank) - hidden and locked). When the end-user inputs yyyy element of the equation, it completes the formula and returns the relevant appraisal month. From experience in these matters, the less my end-users have to do the better and the more chance of it working. Removing the yyyy end-user input would be the preferred method.<o></o>
<o> </o>
Stand-by. I will let you know how I get on. Fingers crossed.<o></o>
<o> </o>
Many thanks for replying.<o></o>