Help needed with Capturing time worked

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Smith - 13 Nov 2006 02:24 GMT

Thanks to many helpful directions from this group I am using the formula

=IF(D9>C9,MAX(0,MIN(D9,"15:00")-MAX(C9,"7:00")),MAX(0,"15:00"-MAX(C9,"7:00"))+MAX(0,MIN(D9,"15:00")-"7:00"))
to capture the hours during a person's regular shift (7:00-15:00) that
may have been worked on a day off.
Column C is for entering the start time.
Column D is for entering the end time.
I manipulated the above formula to attempt to capture the hours outside
the regular shift and arrived at
=IF(D9>=C9,MAX(0,MIN(D9,"24:00")-MAX(C9,"0:00")-MAX(0,"15:00"-MAX(C9,"7:00"))),MAX(0,MIN(D9,"15:00")-MAX(C9,"7:00"))).
To my amazement, it actually works but only for certain hours like
(07:00-17:00).
What do I need to do to make it work on all such hours outside (either
before or after, or both) 7:00 - 15:00?
John

Reply to this Message

Bob Phillips - 13 Nov 2006 09:06 GMT

Assuming the first formula is in E9,

=MOD(D9-C9,1)-E9

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Thanks to many helpful directions from this group I am using the formula

=IF(D9>C9,MAX(0,MIN(D9,"15:00")-MAX(C9,"7:00")),MAX(0,"15:00"-MAX(C9,"7:00")
)+MAX(0,MIN(D9,"15:00")-"7:00"))

> to capture the hours during a person's regular shift (7:00-15:00) that
> may have been worked on a day off.
> Column C is for entering the start time.
> Column D is for entering the end time.
> I manipulated the above formula to attempt to capture the hours outside
> the regular shift and arrived at

=IF(D9>=C9,MAX(0,MIN(D9,"24:00")-MAX(C9,"0:00")-MAX(0,"15:00"-MAX(C9,"7:00")
)),MAX(0,MIN(D9,"15:00")-MAX(C9,"7:00"))).

> To my amazement, it actually works but only for certain hours like
> (07:00-17:00).
> What do I need to do to make it work on all such hours outside (either
> before or after, or both) 7:00 - 15:00?
> John

Reply to this Message

Rate this thread: