Modified IF formula for military time
 

Hi,
I posted a question earlier to figure out a formula that tests whether an
employee was present at a certain hour.

My formula so far is:
=IF(OR(AND($a2<=$b2,c$1=$a2,$b2c$1),AND($a2=$b2 ,OR(c$1=$a2,c$1<$b2))),1,0)

The result is:
Time In Time Out Present @hr 5
Present @hr 20
Employee 1 05:55 20:01 Yes
Yes
Employee 2 22:30 07:15 Yes No
Employee 3 06:05 08:01 No No

I need to change the formula so that if Employee 1 is early 5 minutes he is
not included in the previous hour, and if Employee 1 stays 1 minute late, he
is not included in hour 20.

Modified IF formula for military time
 

Sorry, the margin of error should be 15 minutes. Thanks!

"kmariab" wrote:

Hi,
I posted a question earlier to figure out a formula that tests whether an
employee was present at a certain hour.

My formula so far is:
=IF(OR(AND($a2<=$b2,c$1=$a2,$b2c$1),AND($a2=$b2 ,OR(c$1=$a2,c$1<$b2))),1,0)

The result is:
Time In Time Out Present @hr 5
Present @hr 20
Employee 1 05:55 20:01 Yes
Yes
Employee 2 22:30 07:15 Yes No
Employee 3 06:05 08:01 No No

I need to change the formula so that if Employee 1 is early 5 minutes he is
not included in the previous hour, and if Employee 1 stays 1 minute late, he
is not included in hour 20.

Modified IF formula for military time
 

What's in cells A2, B2 & C1, firstly?

"kmariab" wrote:

Hi,
I posted a question earlier to figure out a formula that tests whether an
employee was present at a certain hour.

My formula so far is:
=IF(OR(AND($a2<=$b2,c$1=$a2,$b2c$1),AND($a2=$b2 ,OR(c$1=$a2,c$1<$b2))),1,0)

The result is:
Time In Time Out Present @hr 5
Present @hr 20
Employee 1 05:55 20:01 Yes
Yes
Employee 2 22:30 07:15 Yes No
Employee 3 06:05 08:01 No No

I need to change the formula so that if Employee 1 is early 5 minutes he is
not included in the previous hour, and if Employee 1 stays 1 minute late, he
is not included in hour 20.

Modified IF formula for military time
 

A2= Time In
B2= Time Out
C1=Test Time (Present at hr X)

"Harimau" wrote:

What's in cells A2, B2 & C1, firstly?

"kmariab" wrote:

Hi,
I posted a question earlier to figure out a formula that tests whether an
employee was present at a certain hour.

My formula so far is:
=IF(OR(AND($a2<=$b2,c$1=$a2,$b2c$1),AND($a2=$b2 ,OR(c$1=$a2,c$1<$b2))),1,0)

The result is:
Time In Time Out Present @hr 5
Present @hr 20
Employee 1 05:55 20:01 Yes
Yes
Employee 2 22:30 07:15 Yes No
Employee 3 06:05 08:01 No No

I need to change the formula so that if Employee 1 is early 5 minutes he is
not included in the previous hour, and if Employee 1 stays 1 minute late, he
is not included in hour 20.