Help with "AND" Formula in Excel 4

[TAngel]

Can someone tell me why my AND forumual isn't working correctly? I should be getting back a value of 0 but am getting a 1 for the month ending of 6/30/09.

What I am trying to do is determine if the employee was eligible as of 6/30/09 based on hire date and termination date. If Elig give me back a value of 1 else 0

Formula =IF(AND($D2>G$1,$E2<G$1),0,1)

D2 is 02/01/09 (Date of Hire)
G1 is 06/30/09 (End of Month)
E2 is 06/15/09 (Termination Date)


Thank you

 

[SkipVought]

Hi,
[tt]
Formula =IF(AND($D2>G$1,$E2<G$1),0,1)

D2 is 02/01/09 (Date of Hire)
G1 is 06/30/09 (End of Month)
E2 is 06/15/09 (Termination Date)
[/tt]
Well D2 is LESS THAN G1, so right off you'll get a 1!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TAngel]

Hi Skip,
Well, since the employee was hired on 2/1/09 then termed on 6/15/09 my end result should be a 0 because he is no longer elig. Do I have my < mixed with my > ?

 

[SkipVought]

Please state your LOGIC!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TAngel]

I am trying to write a formula that looks at the employee's hire date and termination date at the end of each month to get a count of how many eligible employees we had. Elig = 1 not Elig = 0.

Hire Date 2/1/09
Termination Date 6/15/09

1/31/09 should equal 0
2/28/09 thru 5/31/09 should equal 1
6/30/09 should equal 0

 

[SkipVought]

Focus like a LASER!
[tt]
1/31/09 should equal 0

what about 2/1/09 - 2/27/09???

2/28/09 thru 5/31/09 should equal 1

what about 6/1/09 - 6/29/09???

6/30/09 should equal 0
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TAngel]

Skip,
My spreadsheet has a column for each month end. I don't aer about anything in between "aaout 2/1/09 - 2/27/09" or "6/1/09 - 6/29/09". I only care about if they are elig on the last day of each month (or the date in the column heading)

A1 1/31/09
B1 2/28/09
C1 3/31/09
ETC.....

In my example
Formula =IF(AND($D2>G$1,$E2<G$1),0,1)

D2 is 02/01/09 (Date of Hire)
E2 is 06/15/09 (Termination Date)

G1 is 06/30/09 (End of Month)

 

[Gavona]

Adding a bit of logic for employees that have not terminated I think you are after:
=--AND(HireDate<MonthEndDate,or(TerminationDate="",TerminationDate>PreviousMonthEndDate)

(substitute in the relevant cell references)


Gavin

 

[Gavona]

oops, missed the last bracket
=--AND(HireDate<MonthEndDate,or(TerminationDate="",TerminationDate>PreviousMonthEndDate))

Which becomes:
=--AND($D2<G$1,or($E2="",$E2>F$1))

Gavin

 

[SkipVought]

[tt]
=IF(AND($D2<G$1,$E2>=H$1,$E2<G$1),0,1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TAngel]

Gavin,
How do you turn =--AND($D2<G$1,or($E2="",$E2>F$1)) into a 1 & 0 vs a True & False?

Thank you.

 

[TAngel]

Nevermind - I got it! Woot Woot...

=IF(AND($D2<G$1,OR($E2="",$E2>G$1)),1,0)

Thank you all very much for the help. Whew, this was way beyond my ability.

Tee

 

[Gavona]

Excel will do that for you if you apply any mathematical operation to it. That is what the -- did.
equally 1*AND($D2<G$1,or($E2="",$E2>F$1)) would do it.

Gavin

 

[Gavona]

We cross posted. You do NOT need the IF.
--TRUE evaluates to 1
--FALSE evaluates to 0

Gavin

 

[TAngel]

Gavin, Thank you for educating me again! I had no idea what the -- before the formula meant so I left it out.

Skip, Thank you too for all your help.