Jump to content


Photo

Eligibility

tables sorting relationships IF AND TODAY

  • Please log in to reply
5 replies to this topic

#1 mdavids2

mdavids2

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 19 June 2015 - 05:54 PM

Hello,

 

I have data that shows the dates that an employee got hired.  Employees are eligible for insurance for the 1st month following 60 days of continuous employment (we do have employees who get terminated and rehired, so multiple term and rehire dates).  My goal is to create a formula that will look at the dates and if the person is hired and in their eligibility period put a certain code, but once they have passed their eligibility period, then switch to a different code.  I have been trying a few different nested IF, AND, and OR functions, but I can't seem to find what is quite right.  Especially when you consider that an employee could be hired on 1/1/2015, then terminated on 2/3/15, rehired on 6/5/15 and then termed again on 12/20/15.

 

The result of this example would be that I would want EXCEL to give me the following:

 

From 1/1/2015 - 2/3/15 - Code A (hired and in eligibility period)

From 2/3/15 - 6/5/15 - Code B (not employed)

From 6/5/15 - 8/30/15 - Code A (hired and in eligibility period)

From 9/1/15 - 12/19/15 - Code C (eligible)

From 12-20/15 onward - Code B (not employed)

 

Any help or ideas on this would be wonderful.  I'm using Excel 2010.

 

Thank you,

 

Mike



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 20 June 2015 - 03:34 PM

Hi Mike,

 

If I understand correctly, the logic you are trying to model is as follows:

 

If the employee is not currently employed, show code B.

Otherwise, if the employee is employed and has been present for more than 60 days but less than 90 days, show code A.

Otherwise, show code C.

 

To make this easier to follow I have split it into 3 steps, but you could combine it all into a single formula if you needed to.

 

I have attached my example workbook:

 

Attached File  Eligibility.xlsx   9.78KB   232 downloads

Eligibility.png

 

To calculate whether the employee is currently employed (in column E), I use the following formula:

=IF([Finish Date]="",TRUE,IF(AND([Start Date]>=TODAY(),[Finish Date]<=TODAY()),TRUE,FALSE))

 

This first checks whether the finish date is blank and returns TRUE if it is - the employee has not yet been terminated. Otherwise it checks whether the current date is between the start date and finish date, returning TRUE if so and FALSE if not.

 

This is achieved using the IF, AND and TODAY functions.

You can see an introduction to the IF function in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

The AND function is fully explained in the video lesson: Expert Skills Lesson 3-16 Use the AND and OR functions to construct complex Boolean criteria.

TODAY and other common date functions are covered in the video lesson: Expert Skills Lesson 3-8 Use the Excel YEAR function and other common date functions.

 

Next, we calculate whether the employee is eligible with the formula:

=IF(AND([Days Employed]>60,[Days Employed]<=90),TRUE,FALSE)

 

This is another formula with IF and AND that checks whether the employee has been employed for between 60 and 90 days and returns TRUE if so.

 

Finally, we can calculate the correct code with the formula:

=IF([Currently Employed]=FALSE,"B",IF([Eligible],"A","C"))

 

...again, simply using the IF function.

 

I hope this is what you were looking for, but please feel free to reply if you need any further assistance.

You can attach a copy of your workbook if you'd like to me to take a look at it - you can find instructions for attaching workbooks by clicking here.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 mdavids2

mdavids2

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 22 June 2015 - 04:52 PM

Hi Jonathan,

 

I think I follow your response and it is what I need.  The only change is the fact that I need to know what code the employee would have used as of certain date (first of each month).  So it's based on historical data (or at least will be by the end of the year).  Lastly, is that there are multiple hire and rehire dates.  I made a quick excel workbook similar to what you have.  The yellow is where I need to put the codes based on the hiring and term dates.

 

Ex:  In the file you will see that Jonathan started on 1/1/15, but finished on 2/3/15.  But, how how do we change the formula in regards to a rehire date during the same year?

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 22 June 2015 - 09:18 PM

Hi again Mike,

 

Your initial specification was that eligibility only applies after 60 days of continuous employment, meaning that termination and re-hire would not affect the formula as it applies equally to any individual period of employment.

 

I would suggest modifying your layout so that each row represents a period of employment rather than trying to have one row per employee with varying numbers of terms of employment.  In database terms, we would say that one employee can have many terms of employment, making this a "1 to many" relationship. As such, you might consider splitting the data into separate tables, but in this simple example that's not really necessary.

 

You can find a more detailed explanation of one to many relationships in the video lesson: Expert Skills Lesson 6-1 Understand primary and foreign keys.

 

After splitting the terms of employment into single rows, it's quite straightforward to create a formula based upon the previous example.  I've attached a new example based on your data:

Attached File  Eligibility Question.xlsx   10.37KB   165 downloads

termsofemployment.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 mdavids2

mdavids2

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 22 June 2015 - 11:22 PM

Hi Jonathan,

 

Thank you for your response.

 

The only issue i'm having now is that my end deliverable needs to be a list of each employee with the codes listed for each month.

 

How would I then be able to take the resulting data from the tables and merge them into a format where I could have the employees (listed only once) along the left axis and the months across the top? with the codes data in the middle?

 

Or is this not possible with Excel?

 

Thank you,

 

Mike



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 23 June 2015 - 09:06 PM

Hi Mike,

 

As far as I can tell, the only period of employment that is relevant when calculating codes, so you could get something approaching what you need by simply sorting the data by name and date.

 

Sorting is covered in the video lesson: Expert Skills Lesson 1-14 Sort a range or table by rows.

 

Creating relationships and joining tables is something that is covered in Expert Skills Session 6: The Data Model, OLAP, MDX and BI, but it would probably be overkill in this case.

 

I can't think of a way to gracefully create the kind of interface you're looking for, at least not in a way that would be resilient to unusual occurences, such as employees being terminated and rehired several times. It may be that Excel is too limited to deal with the amount of logic that would be needed to create what you need, but you might be able to create a system that serves your purposes by using sorting or by manually rearranging the workbook if it is relatively small and doesn't need to be created often.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users