military time "if" formula

[kmariab]

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?

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bob I]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bob Phillips]

=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]

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

 

[kmariab]

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

 

[Bob Phillips]

Does mine?

--
---
HTH

Bob


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

 

[Bob I]

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