Help needed with Capturing time worked

J

John Smith

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
 
B

Bob Phillips

Assuming the first formula is in E9,

=MOD(D9-C9,1)-E9

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top