Nested IF using dates questions

[Grant22]

Hello all, I have a formula that works fine as it is now, but my company went and changed the criteria so I need to add more levels to this formula. Basically, in Cell M1 is the hire date and Cell M2 is the status which is always Part Time or Full Time. Right now the formula uses the date as a number to calculate the number of vacation hours earned (I.E. if they've been there more than 15 years and they're full time they it calculates 200 hours).

But I'm needing to add another level where if they where hired before April 30th of the current year they get 80 hours, if hired between May 1st and August 31st they get 40 hours and after September 1st they get 0. The formula below works fine but I'm not sure how to add the extra checks into the IF statement.

Note: The formula is all written as one line in the excel FX box, I just broke it into single lines cause I thought it would be easier to read.

Any help is greatly appreciated.

=IF(ISBLANK(M1),0,
IF(AND(M2="Part Time",M1<(TODAY()-5475)),137,
IF(AND(M2="Part Time",M1<(TODAY()-2555)),104,
IF(AND(M2="Part Time",M1<(TODAY()-1825)),80,
IF(AND(M2="Part Time",M1<(TODAY()-1)),48,
IF(AND(M2="Full Time",M1<(TODAY()-5475)),200,
IF(AND(M2="Full Time",M1<(TODAY()-2555)),160,
IF(AND(M2="Full Time",M1<(TODAY()-1825)),120,
IF(AND(M2="Full Time",M1<(TODAY()-1)),80,0
)))))))))

'or the correct way:

=IF(ISBLANK(M1),0,IF(AND(M2="Part Time",M1<(TODAY()-5475)),137,IF(AND(M2="Part Time",M1<(TODAY()-2555)),104,IF(AND(M2="Part Time",M1<(TODAY()-1825)),80,IF(AND(M2="Part Time",M1<(TODAY()-1)),48,IF(AND(M2="Full Time",M1<(TODAY()-5475)),200,IF(AND(M2="Full Time",M1<(TODAY()-2555)),160,IF(AND(M2="Full Time",M1<(TODAY()-1825)),120,IF(AND(M2="Full Time",M1<(TODAY()-1)),80,0)))))))))

 

[Grant22]

Anyone able to help?

 

[Joe4]

I don't know that your question is quite clear.
If you are checking for dates in the current year, why are you subtracting large numbers from the current day, like 1825, 2555, and 5475?
Can you just list out all the exact rules in plain English?

One way would be to create your own function in VBA (a User Defined Function or "UDF").
The advantage there is:
- it is much easier to read
- it is much easier to maintain
- if you ever need to update/change it, you only need to change it one place (you don't need to update multiple formulas all over your sheets)
Are you open to that type of solution?

 

[Grant22]

Thanks for the reply, yes I'm open to coding it, I just don't have a lot of experience with dates in VBA so I did it that way originally. The answer to your question is 5475 is 15 years worth of days (365 x 15 = 5475) so if the hire date is older than that, the formula will return 200 vacation hours.

But yeah, any pointers with with a sub to do the same thing would be very welcome!

 

[Eric W]

Joe4 raises several excellent points about a UDF - it would be easier to understand and maintain. That said, let me offer some formulas.

First:

=LOOKUP(DATEDIF(M1,TODAY(),"y"),{0,5,7,15,99},{48,80,104,137,0}+{32,40,56,63,0}*(M2="Full Time"))

This is just a replacement for the formula you gave. Much shorter, and easier to read (I think). The DATEDIF is used to find the number of years between today and M1. The first array constant {0,5,7,15,99} is the year cutoffs for each level. (99 is there in case M1 is blank.) The LOOKUP finds the year in the first array, pulls the number from the second array, and the third array is the number of hours different from part and full time.


Now to adapt that to your latest request, I came up with this:

=IF(YEAR(M1)=YEAR(TODAY()),INT((12-MONTH(M1))/4)*40,LOOKUP(DATEDIF(M1,TODAY(),"y"),{0,5,7,15,99},{48,80,104,137,0}+{32,40,56,63,0}*(M2="Full Time")))

If the year of M1 is the same as the year of TODAY, I created a formula to calculate the hours. Otherwise, it defaults to the previous formula. The problem is that the formula for hours this year is not especially obvious. In a UDF you can add comments to make it easier to understand. Nevertheless, this might be worth looking at.

 

[Joe4]

But I'm needing to add another level where if they where hired before April 30th of the current year they get 80 hours, if hired between May 1st and August 31st they get 40 hours and after September 1st they get 0. The formula below works fine but I'm not sure how to add the extra checks into the IF statement.

This looks like it is for the full-time people (as you mention 80 hours).
What about the rule for part-time people hire this year?

 

[Joe4]

OK, for part-time, I just followed the same pattern and gave them half of 48 if they were hired between May and August of the current year.
Here is what that function looks like. It looks like, but that is mostly because it is in outline form, which should make it easier to read, follow, and edit, if need be:

Function Vacation(HireDate As Date, Status As String) As Integer

    Dim Service As Integer
    Dim SameYear As Boolean
    Dim HireMonth As Integer
    
    Application.Volatile
    
'   Calculate full years of service
    Service = Date - HireDate
    
'   See if hired in current year, and get hire month
    SameYear = (Year(Date) = Year(HireDate))
    HireMonth = Month(HireDate)
    
'   Check status
    Select Case UCase(Status)
'       Full Time calculations
        Case "FULL TIME"
'           See if hired during the year
            If SameYear Then
                Select Case HireMonth
                    Case Is > 9
                        Vacation = 0
                    Case 5 To 8
                        Vacation = 40
                    Case 1 To 4
                        Vacation = 80
                End Select
            Else
'               Otherwise, do calculation for multiple years
                Select Case Service
                    Case Is >= 5475
                        Vacation = 200
                    Case 2555 To 5474
                        Vacation = 160
                    Case 1825 To 2554
                        Vacation = 120
                    Case Else
                        Vacation = 80
                End Select
            End If
'       Part time calculations
        Case "PART TIME"
'           See if hired during the year
            If SameYear Then
                Select Case HireMonth
                    Case Is > 9
                        Vacation = 0
                    Case 5 To 8
                        Vacation = 24
                    Case 1 To 4
                        Vacation = 48
                End Select
            Else
'               Otherwise, do calculation for multiple years
                Select Case Service
                    Case Is >= 5475
                        Vacation = 137
                    Case 2555 To 5474
                        Vacation = 104
                    Case 1825 To 2554
                        Vacation = 80
                    Case Else
                        Vacation = 48
                End Select
            End If
'       Return 0 for invalid status
        Case Else
            Vacation = 0
    End Select
    
End Function

So then you would just use it like any other function in Excel.
So, if you had hire date in cell A1, and status in cell B1, the formula you would use would just be:
=Vacation(A1,B1)

 

[Grant22]

Wow, thanks for the help guys, that's why I love this place!

 

[Joe4]

You are welcome.

 

[Grant22]

Joe that worked like a charm, I always wanted to do it with code but my VBA skills are rusty, I really appreciate your help in walking me through this!