Determine if "Active" if hire and term date are within cells range

  • I am by far from being an expert, but I can usually figure things out. But NOT this time.
    I'm trying to build a spreadsheet (that will be part of a database) that determine if someone was employed or "Active" for a month. The active monthly employees will be on an actively monthly table that will count the potential number of project from another file.


    I have several with peoples names and their hire and term (termination) dates listed across several rows. The dates need to be compared against cells for the first and last day of the month, which is on row 2, cell I2 is start of month and J2 is last day of the month. Below this is the employee hire and term dates.


    What I'm trying to have happen is that the word "Active" would then show on each persons row for that month. Some people have hire and term dates and some only have hire dates, which means that they are currently active but may not have been for the month range on cells I2 and J2.


    Basically, I trying to merge a SQL table ( that is very incomplete with missing date) against a spreadsheet to build a table for Access, but that's the easy part. I just can figure out my formula to determine if someone is active for a month.
    Any help would greatly appreciated.


    I tried posting on another site... http://www.excelforum.com/exce…31760-if-or-and-help.html but I either worded everything wrong and just wasn't understood. Hope I do that here.

  • Re: Determine if "Active" if hire and term date are within cells range


    Hi Delta729,


    Welcome to the Ozgrid forum.


    Try looking at it the other way round, and work out who is not active:


    =IF(OR(AND(N([@Term])>0,[@Term]<$I$2),[@Start]>$J$2), "","Active")


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Determine if &quot;Active&quot; if hire and term date are within cells range


    Got it a few days ago, sorry for not responding and thanks for the reply.


    =IF(OR(AND($J$2>[@Start],$J$2<[@Term]),AND($K$2<[@Term],$K$2>[@Start]),AND([@Term]="",[@Start]<$K$2)),"Active","")