i have employees sign-in and sign-out using military time. I want to be able
to calculate if each employee was present at a certain hour. For example:
Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present between
those times?

With the Sign-in time in B13, Sign-out time in B14 and the Test time in B15
try:

=IF(OR(B15=B13,B15<=B14),"Yes","No")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"kmariab" wrote in message
...
i have employees sign-in and sign-out using military time. I want to be
able
to calculate if each employee was present at a certain hour. For example:
Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present between
those times?

I see a problem already as both instances should be Yes. Also you would
need the date as part of the sheet.

kmariab wrote:

i have employees sign-in and sign-out using military time. I want to be able
to calculate if each employee was present at a certain hour. For example:
Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present between
those times?

Of course it would have been better if I had tested it duting *normal*
times! It on;y works on overnight times!

Doh!

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
With the Sign-in time in B13, Sign-out time in B14 and the Test time in
B15 try:

=IF(OR(B15=B13,B15<=B14),"Yes","No")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"kmariab" wrote in message
...
i have employees sign-in and sign-out using military time. I want to be
able
to calculate if each employee was present at a certain hour. For
example:
Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present between
those times?

=IF(OR(AND($A$1<=$A$2,$A3=$A$1,$A$2=$A3),AND($A$ 1=$A$2,OR($A3=$A$1,$A1=$A$4))),"Yes","No")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kmariab" wrote in message
...
i have employees sign-in and sign-out using military time. I want to be
able
to calculate if each employee was present at a certain hour. For example:
Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present between
those times?

i have employees sign-in and sign-out using military time. I want to
be able
to calculate if each employee was present at a certain hour. For
example: Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present
between those times?

With the Sign-in time in B13, Sign-out time in B14 and the Test time
in B15 ...

Maybe something like this would work for you:

=IF(B14<B13,IF(OR(B15=B13,B15<=B14),"Yes","No"),
IF(AND(B15B13,B15<B14),"Yes","No"))

Of course, this doesn't work if somebody stays for more than 24 hours.

Sorry, I had meant to type in: was the employee present at 15:00?, and the
answer to be NO.

"Bob I" wrote:

I see a problem already as both instances should be Yes. Also you would
need the date as part of the sheet.

kmariab wrote:

i have employees sign-in and sign-out using military time. I want to be able
to calculate if each employee was present at a certain hour. For example:
Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present between
those times?

Thanks for your help. I tried the formula, but it doesn't seem to work for
overnight shifts.

"MyVeryOwnSelf" wrote:

i have employees sign-in and sign-out using military time. I want to
be able
to calculate if each employee was present at a certain hour. For
example: Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present
between those times?

With the Sign-in time in B13, Sign-out time in B14 and the Test time
in B15 ...

Maybe something like this would work for you:

=IF(B14<B13,IF(OR(B15=B13,B15<=B14),"Yes","No"),
IF(AND(B15B13,B15<B14),"Yes","No"))

Of course, this doesn't work if somebody stays for more than 24 hours.

Does mine?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kmariab" wrote in message
...
Thanks for your help. I tried the formula, but it doesn't seem to work
for
overnight shifts.

"MyVeryOwnSelf" wrote:

i have employees sign-in and sign-out using military time. I want to
be able
to calculate if each employee was present at a certain hour. For
example: Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present
between those times?

With the Sign-in time in B13, Sign-out time in B14 and the Test time
in B15 ...

Maybe something like this would work for you:

=IF(B14<B13,IF(OR(B15=B13,B15<=B14),"Yes","No"),
IF(AND(B15B13,B15<B14),"Yes","No"))

Of course, this doesn't work if somebody stays for more than 24 hours.

I believe you will need to supply dates for all times involved other
wise you have a problem covering the 2 day shifts.

kmariab wrote:

Sorry, I had meant to type in: was the employee present at 15:00?, and the
answer to be NO.

"Bob I" wrote:


I see a problem already as both instances should be Yes. Also you would
need the date as part of the sheet.

kmariab wrote:


i have employees sign-in and sign-out using military time. I want to be able
to calculate if each employee was present at a certain hour. For example:
Sign-in: 8:00
Sign-out: 14:00
Was employee present at 9:00? Yes
Was employee present at 13:00? No

My problem arises when employee works overnight. For example:
Sign-in: 18:00
Sign-out: 3:00

Is there a formula that recognizes whether employee was present between
those times?