Hi



I haven't been on the forum for a while but in desperate need of help.

I have a spreadsheet that I use to track if my team is meeting specific targets. I need a formula that will do the following:-

1. Will return the number of working days from start to end date. Now I have a basic one that is working i.e. =IF(A2="","",IF(ISTEXT(A2),0,NETWORKDAYS(A2,P2,Hol idays))

2. However, the target number of working days is different dependent on whether the team are preparing a variation letter or a contract. So if it's a variation letter that is being prepared then the team have 5 working days to action. However, if it is a contract, they have 8 weeks from employee's start date to complete this. How can I tweak the formula I am using which is =IF(KR2="","",IF((KR2<=5),"Yes","No")), to allow for 8 weeks if it's a contract?

3. There are three different types of contract i.e. Permanent Contract, Temporary Contract No Right to Revert and Temporary Contract with Right to Revert. This value is in cell KL2 as a drop down pick list.

Thoughts, musings?

I'm at a loss.

Lynn

I am not sure what you want.

Is it the formula =IF(KR2="","",IF((KR2<=5),"Yes","No")) that you gives you problems?

Can't you try to describe what exactly you want returned if certain criteria are met?

Thanks for the quick response.

I have a networkdays formula which is working fine ... i then have the formula to show if days are <=5 then target met. This is fine for a letter but if it's a contract then the target is 8 weeks (40 working days)...so need a fix for this formula

=IF(KR2="","",IF((KR2<=5),"Yes","No"))

the type of document to be prepared is in cell KL2 and is a drop down...so user would pick from following list

Permanent Contract
Temporary Contract No Right to Revert
Temporary Contract with Right to Revert
Variation Letter
Sessional Worker Letter

So 5 day KPI for last 2 and 40 for the first 3...

I don't know if I'm making any sense...I hope so!

Lynn

Don't know if this is of any help.

Attached Files

LynnMac.xlsx (16.1 KB, 11 views)

Hi

I'm so sorry for confusing things...I have attached an example of my spreadsheet with an explanation of what I'm trying to do...hope this helps...

Thank you for responding.

Lynn

Attached Files

LynnMac2016.xlsx (16.0 KB, 10 views)

Is it better now?

Attached Files

LynnMac2016_2.xlsx (19.5 KB, 12 views)

OMG this totally works ... thank you so so much!!!!

Hi

I might be pushing my luck here but here goes:-

The solution that xor provided worked perfectly until one of my colleagues said that the 40 day KPI for contracts kicks in at a different date from start date for recommended candidate. How can the attached formula be tweaked to take in this scenario?

Any assistance greatly appreciated.

Lynn

Attached Files

LynnMac2016_2 (3).xlsx (15.3 KB, 10 views)

Can't you try to (manually) give more examples in the sheet og expected results maybe with an extra cell for start date for some candidates? I find it a bit difficult to understand what you want.