Filling in the gaps

[n1lesh]

i was wondering if some one could help me with the following problem and thanks in advance

[TABLE="width: 1224"]
<colgroup><col width="72" span="17" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 72"][/TD]
[TD="class: xl63, width: 72"][/TD]
[TD="class: xl63, width: 72"][/TD]
[TD="class: xl66, width: 72"]TIME[/TD]
[TD="class: xl65, width: 72"]20:00[/TD]
[TD="class: xl65, width: 72"]21:00[/TD]
[TD="class: xl65, width: 72"]22:00[/TD]
[TD="class: xl65, width: 72"]23:00[/TD]
[TD="class: xl65, width: 72"]00:00[/TD]
[TD="class: xl65, width: 72"]01:00[/TD]
[TD="class: xl65, width: 72"]02:00[/TD]
[TD="class: xl65, width: 72"]03:00[/TD]
[TD="class: xl65, width: 72"]04:00[/TD]
[TD="class: xl65, width: 72"]05:00[/TD]
[TD="class: xl65, width: 72"]06:00[/TD]
[TD="class: xl65, width: 72"]07:00[/TD]
[TD="class: xl65, width: 72"]08:00[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65"]21:00[/TD]
[TD="class: xl65"]22:00[/TD]
[TD="class: xl65"]23:00[/TD]
[TD="class: xl65"]00:00[/TD]
[TD="class: xl65"]01:00[/TD]
[TD="class: xl65"]02:00[/TD]
[TD="class: xl65"]03:00[/TD]
[TD="class: xl65"]04:00[/TD]
[TD="class: xl65"]05:00[/TD]
[TD="class: xl65"]06:00[/TD]
[TD="class: xl65"]07:00[/TD]
[TD="class: xl65"]08:00[/TD]
[TD="class: xl65"]09:00[/TD]
[/TR]
[TR]
[TD="class: xl66"]START [/TD]
[TD="class: xl66"]FINISH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]22:10[/TD]
[TD="class: xl65"]06:00[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64"]00:10[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl65"]20:25[/TD]
[TD="class: xl65"]07:20[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64"]00:25[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]01:00[/TD]
[TD="class: xl64"]00:20[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

i have been tasked to fill out the following sheet i hope i can explain it to you:
i am looking for a formula that will out this sheet for me

for example 1 some one starts at 22:10 so in the time column i have to enter in 00:10 mins from 22:00-23:00
they finish at 6:00 so i have to enter in 1:00 in the column 05:00-06:00
all the rows in between have 01:00 in them as they are at work

i have thousands of these lines to fill out and was wondering if there was a formula to help me with this preferably not VBA
Again thanks in adavance if this is possible

 

[thisoldman]

Try this.

Top left cell is A1.

<tbody>
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, width: 40"]TIME[/TD]
[TD="class: xl67, width: 40, align: right"]20:00[/TD]
[TD="class: xl67, width: 40, align: right"]21:00[/TD]
[TD="class: xl67, width: 40, align: right"]22:00[/TD]
[TD="class: xl67, width: 40, align: right"]23:00[/TD]
[TD="class: xl67, width: 40, align: right"]00:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]02:00[/TD]
[TD="class: xl67, width: 40, align: right"]03:00[/TD]
[TD="class: xl67, width: 40, align: right"]04:00[/TD]
[TD="class: xl67, width: 40, align: right"]05:00[/TD]
[TD="class: xl67, width: 40, align: right"]06:00[/TD]
[TD="class: xl67, width: 40, align: right"]07:00[/TD]
[TD="class: xl67, width: 40, align: right"]08:00[/TD]

[TD="class: xl66, width: 47"][/TD]
[TD="class: xl66, width: 47"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl67, width: 40, align: right"]21:00[/TD]
[TD="class: xl67, width: 40, align: right"]22:00[/TD]
[TD="class: xl67, width: 40, align: right"]23:00[/TD]
[TD="class: xl67, width: 40, align: right"]00:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]02:00[/TD]
[TD="class: xl67, width: 40, align: right"]03:00[/TD]
[TD="class: xl67, width: 40, align: right"]04:00[/TD]
[TD="class: xl67, width: 40, align: right"]05:00[/TD]
[TD="class: xl67, width: 40, align: right"]06:00[/TD]
[TD="class: xl67, width: 40, align: right"]07:00[/TD]
[TD="class: xl67, width: 40, align: right"]08:00[/TD]
[TD="class: xl67, width: 40, align: right"]09:00[/TD]

[TD="class: xl66, width: 47"]START[/TD]
[TD="class: xl66, width: 47"]FINISH[/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]

[TD="class: xl67, width: 47, align: right"]22:10[/TD]
[TD="class: xl67, width: 47, align: right"]06:10[/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl67, width: 40"][/TD]
[TD="class: xl67, width: 40"][/TD]
[TD="class: xl67, width: 40, align: right"]00:10[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]00:10[/TD]
[TD="class: xl67, width: 40"][/TD]
[TD="class: xl67, width: 40"][/TD]

[TD="class: xl67, width: 47, align: right"]20:25[/TD]
[TD="class: xl67, width: 47, align: right"]07:20[/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl66, width: 40"][/TD]
[TD="class: xl67, width: 40, align: right"]00:25[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]01:00[/TD]
[TD="class: xl67, width: 40, align: right"]00:20[/TD]
[TD="class: xl67, width: 40"][/TD]

</tbody>

In E4, copied to the left and downward:
=IF(HOUR($A4+4/24)=HOUR(E$1+4/24),$A4-E$1,IF(HOUR($B4+4/24)=HOUR(E$1+4/24),$B4-E$1,
IF(AND(HOUR($A4+4/24)< HOUR(E$1+4/24),HOUR($B4+4/24)>HOUR(E$1+4/24)),TIME(1,0,0),"")))

 

[n1lesh]

i don't know how to thank you for this you have saved me so much time

 

[thisoldman]

Glad to help. Thanks for not commenting that I don't know right from left.

 

[thisoldman]

I looked at this again. In the first row, should the time difference be 00:50 in the 22:00 to 23:00 hour—start at 22:10 and end at 23:00 equals 50 minutes?
Working from 20:25 to 21:00 in column E should be 35 minutes? If so, try this.

=IF(HOUR($A4+4/24)=HOUR(E$1+4/24),TIME(1,0,0)-TIME(0,MINUTE($A4),0),IF(HOUR($B4+4/24)=HOUR(E$1+4/24),$B4-E$1,
IF(AND(HOUR($A4+4/24)< HOUR(E$1+4/24),HOUR($B4+4/24)>HOUR(E$1+4/24)),TIME(1,0,0),"")))