IF statement to compare 2 columns and add to a date

[yido]

Hi guys, I am no excel expert, only to A Level standard and am really struggling to figure out a formula or macro. I need to work out the length of notice to give to employees that are being dismissed by comparing 2 sets of criteria and working out which would be more beneficial to the employee, if that makes sense? (I am assuming the formula will be really complicated!)

I need to work out what the employees last working day will be.
Say we dismiss on 23rd May
+ 2 days (for postage of letters)
+ extra notice. This is worked out comparing the employees length of continuous service versus grade/salary. The formula needs to compare the two and work out which is most beneficial to the employeee.
1 years employment = 1 weeks notice
12 years employment = 12 weeks notice (no matter what grade)
grade 6 and below = 4 weeks notice
grade 7-11 = 8 weeks notice
grade 12+ = 12 weeks notice
The formula needs to work which would give the employee a longer notice period then add the notice required to the dismissal date (23rd may) and calculate the employees last working day.

For example if i had to dismiss an employee on 23rd May and they had worked for 2 years on grade 6 they would get 2 days plus 4 working weeks, so their last day would be 22 June.
But if they had worked for 12 years on grade 6 they would get 12 weeks notice plus the 2 extra days.

Any help would be really appreciated as I can't seem to get the formula to work!

Thank you! <!-- / message --><!-- attachments -->

 

[Comfy]

Try this UDF.

When testing this my Length of service was in weeks but you could change it to years or days

Function Dismissal(dDate As Long, LengthOS As Long, pGrade As Long)
    If LengthOS <= 623 Then
        Select Case pGrade
            Case 1 To 6
                Dismissal = dDate + 30
            Case 7 To 11
                Dismissal = dDate + 58
            Case Is >= 12
                Dismissal = dDate + 86
            Case Else
                Dismissal = CVErr(xlErrRef)
        End Select
    Else
        Dismissal = dDate + 86
    End If
End Function

/Comfy

 

[AlphaFrog]

Hi and welcome to the board.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">Years of service</td><td style=";">Grade</td><td style=";">Dismissal Date</td><td style=";">Notice</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Bob</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td><td style="text-align: right;;">Monday, May 23, 2011</td><td style="text-align: right;;">Wednesday, June 22, 2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Jim</td><td style="text-align: right;;">12</td><td style="text-align: right;;">6</td><td style="text-align: right;;">Monday, May 23, 2011</td><td style="text-align: right;;">Wednesday, August 17, 2011</td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=D2 + MAX(<font color="Blue">B2, LOOKUP(<font color="Red">C2,{0,7,12},{4,8,12}</font>)</font>)*7 + 2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=D3 + MAX(<font color="Blue">B3, LOOKUP(<font color="Red">C3,{0,7,12},{4,8,12}</font>)</font>)*7 + 2</td></tr></tbody></table></td></tr></table><br />

The formulas above wouldn't take into account weekends e.g. if the dismissal date is a Friday, the notice date would be on a Sunday (+2). Is that an issue? It could be fixed if it is.

 

[yido]

Thank you for the quick reply.

Unfortunately I am having a bit of trouble getting UDF to work, as I haven't used this in a while.
Do I run a module in VBE and once the code has been copied, then type "=module1" in the cell for example?

Do i need to set the spreadsheet up like (column A-D);
Date, Length of Service, Grade, then this UDF?

Sorry for being novicey!
Thank you

 

[yido]

Hi AlphaFrog,
Thank you that looks brilliant!
But yes it needs to be in working weeks (no weekends) if that is possible?
Thank you

 

[Comfy]

Paste the UDF into a module.

Using alpha frogs grid the formula would look like
In your cell put =Dismissal(D2,B2,C2)

You can set the spreadsheet up how you wish just make sure that the 1 reference in the formula is for Dismissal date, 2nd for Length of Service and 3rd for Grade.

/Comfy

 

[AlphaFrog]

If the dismissal date is a Thursday or Friday, this will calculate the Notice date (+weeks+2 days) to fall on a Monday instead of the weekend.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">Years of service</td><td style=";">Grade</td><td style=";">Dismissal Date</td><td style=";">Notice</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Bob</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td><td style="text-align: right;;">Thursday, May 26, 2011</td><td style="text-align: right;;">Monday, June 27, 2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Jim</td><td style="text-align: right;;">12</td><td style="text-align: right;;">6</td><td style="text-align: right;;">Friday, May 27, 2011</td><td style="text-align: right;;">Monday, August 22, 2011</td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=D2 + MAX(<font color="Blue">B2, LOOKUP(<font color="Red">C2,{0,7,12},{4,8,12}</font>)</font>)*7 + 2 + CHOOSE(<font color="Blue">WEEKDAY(<font color="Red">D2</font>),0,0,0,0,2,1,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=D3 + MAX(<font color="Blue">B3, LOOKUP(<font color="Red">C3,{0,7,12},{4,8,12}</font>)</font>)*7 + 2 + CHOOSE(<font color="Blue">WEEKDAY(<font color="Red">D3</font>),0,0,0,0,2,1,0</font>)</td></tr></tbody></table></td></tr></table><br />

 

[yido]

That is awesome thanks so much!