# If Or And help

1. ## 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

2. ## Re: If Or And help

this ..

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

3. ## 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:
`Please Login or Register  to view this content.`

4. ## 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. ## 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. ## 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.

7. ## 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",""))

8. ## 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. ## 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. ## 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","")

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

#### 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