Hi guys, I am having trouble with a formula if anybody can help please.

I have two cells A1 & B1 that holds start time and end time of work done. I then have another two cells C1 & D1. C1 is normal hours worked and D1 is overtime. I am looking for two formula's. For example lets use below data:
A1 B1
05:00 19:00

C1 should work out the time between 08:00 and 17:00 and display "09:00" hours

D1 should work out overtime, (hours worked before 08:00 and after 17:00) and display "05:00"

Ive been running around with If, And and subrtracting MAX statements but cant find one that works. Please help!
Conrad

Hi Conrad,

Am Tue, 30 Jun 2015 00:27:34 -0700 (PDT) schrieb :

A1 B1
05:00 19:00

C1 should work out the time between 08:00 and 17:00 and display "09:00" hours

D1 should work out overtime, (hours worked before 08:00 and after 17:00) and display "05:00"

in C1:
=TIME(17,,)-TIME(8,,)
in D1:
=B1-A1-C1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

... A1 & B1 that holds start time and end time of work done.

C1 should work out the time between 08:00 and 17:00

D1 should work out overtime, (hours worked before 08:00 and after 17:00)

If a shift cannot span midnight, the following might work:

C1:
=MIN(B4,TIME(17,,))-MAX(A4,TIME(8,0,))

D1:
=B4-A4-C4

... A1 & B1 that holds start time and end time of work done.

C1 should work out the time between 08:00 and 17:00

D1 should work out overtime, (hours worked before 08:00 and after 17:00)

If a shift cannot span midnight, the following might work:

C1:
=MIN(B1,TIME(17,,))-MAX(A1,TIME(8,0,))

D1:
=B1-A1-C1