+ Reply to Thread
Results 1 to 10 of 10

If Or And help

  1. #1
    Forum Contributor
    Join Date
    02-24-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 2010
    Posts
    164

    If Or And help

    I've just spent 3 days merging names, their division or department, location with the start and termination dates. There were a lot of start and term dates missing, so I just added a "?" for those rows.

    Now, I'm trying to determine if they were active for a given month. I would like to type in the first and last day of the month on the first row on cells H1 and I1 and have the active column update. I have part of this, but still missing something in my formula for the "Active" column. I could use some help.

    See attached
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If Or And help

    this ..

    =IF(AND([@Term]>=$H$1,[@Term]<=$I$1), "Active","")

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: If Or And help

    I am making some assumptions here as you haven't given much detail. My assumption is that you are wanting to have column J return the word Active if the date in column H is earlier the the date in cell I1 (ie. they started before the end of period you are looking at), and if their termination date is after the date in cell H1 (ie. they finished after the start of the period you are looking at), or is blank (ie. they have not been terminated).

    In that case the following formula will work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  4. #4
    Forum Contributor
    Join Date
    02-24-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 2010
    Posts
    164

    Re: If Or And help

    Argh... Sorry, coffee hadn't kicked in yet. Cell H1 would be the first day of the month and cell I1 would be the last day of the month. If there is a "?" in either of the cells, they would not be included as Active as I don't have their start or term dates yet (or one or the other), but should in the dates in the next few days. If there is a start date, but no end date, they would be active as of the start of date in H1, but no longer active as of the date on I1.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If Or And help

    Not clear on this ...

    If there is a start date, but no end date, they would be active as of the start of date in H1, but no longer active as of the date on I1
    .... as we cannot compare against I1

  6. #6
    Forum Contributor
    Join Date
    02-24-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 2010
    Posts
    164

    Re: If Or And help

    Okay, I've been away all week and have rebuilt the sheet I had so far. Each row now list an individual person by their start and term (termination) date of employment. Some people have a start date and no Term date, meaning that they are currently active, however may not be "Active" for the month that I'm looking at. I will be extracting data by the word "Active" in the Active column. I'll be pulling data one month at a time by typing in the first and last days of the month in cells I2 (1st of month) and J2 (last of month). There will be more added to the file and this will count the total number project a department handles by month and will match up to their "Billing_Number", however this data is not in this file yet.

    Basically, all I'm trying to determine if someone was "Active" for a particular month. I've hoped I've explained it better this time.
    Attached Files Attached Files
    Last edited by Delta729; 03-26-2016 at 03:18 AM.

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If Or And help

    I think this is what you are looking for (?)... =IF([@Term]="","Active",IF(AND([@Start]<=$I$2,[@Term]>=$J$2),"Active",""))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  8. #8
    Forum Contributor
    Join Date
    02-24-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 2010
    Posts
    164

    Re: If Or And help

    Not quite there, this shows people as "Active" if there is no term date in the Term column and for people who hadn't started working by the start date on I2

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: If Or And help

    Try

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

    shows 1 person active for jan 2014 in your posted file

  10. #10
    Forum Contributor
    Join Date
    02-24-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 2010
    Posts
    164

    Re: If Or And help

    I just remembered I posted here a few days ago and didn't post that I had found the answer...

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1