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