Clock In & Clock Out

[Vanda_a]

Dear All. Please kindly advise what formula is used with my situation?

My company start working on 08:30 to 12:00. And start work again on 13:00 to 17:30.

Alex is over 1 min and Jonh is 5 min. I would like to have a formula at column F.

	A	B	C	D	E	F
1		Clock In	Clock Out	Clock In	Clock Out
2	Alex	8:31	12:01	12:59	17:31	1
3	Jonh	8:29	11:55	12:50	17:30	5
4

<tbody>
</tbody>

Many many thanks

 

[exceldevs]

Hi Vanda_a,

Try this.

	A	B	C	D	E	F
1		Clock In	Clock Out	Clock In	Clock Out
2	Alex	8:31	12:01	12:59	17:31	0:01
3	Jonh	8:29	11:55	12:50	17:30	0:05

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 =IF($B2>TIME(8,30,0),$B2-TIME(8,30,0))+IF($C2<time<font color="#008000">(12,0,0),TIME(12,0,0)-$C2)+IF($D2>TIME(13,0,0),$D2-TIME(13,0,0))+IF($E2<time<font color="#008000">(17,30,0),TIME(17,30,0)-$E2)</time</time <tbody> </tbody>

<tbody>
</tbody>

 

[Vanda_a]

Hi Vanda_a, Try this.

	A	B	C	D	E	F
1		Clock In	Clock Out	Clock In	Clock Out
2	Alex	8:31	12:01	12:59	17:31	0:01
3	Jonh	8:29	11:55	12:50	17:30	0:05

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 =IF($B2>TIME(8,30,0),$B2-TIME(8,30,0))+IF($C2<time<font color="#008000">(12,0,0),TIME(12,0,0)-$C2)+IF($D2>TIME(13,0,0),$D2-TIME(13,0,0))+IF($E2<time<font color="#008000">(17,30,0),TIME(17,30,0)-$E2)</time</time <tbody> </tbody>

<tbody>
</tbody>

Many many thanks. May i revise my column please? I would like to use only B1 and C1. Two rows for a day.

Ex:

	A	B	C	D
1		Clock In	Clock Out
2	Alex	8:29	11:59	1
3	Alex	13:01	17:30	1
4	John	8:31	11:59	2
5	John	13:00	16:59	1

<tbody>
</tbody>

Many many thanks

 

[exceldevs]

See if this is what you're looking for?

	A	B	C	D
1		Clock In	Clock Out
2	Alex	8:29	11:59	0:01
3	Alex	13:01	17:30	0:01
4	John	8:31	11:59	0:02
5	John	13:00	16:59	0:31

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =IF(ISEVEN(ROW()),IF($B2>TIME(8,30,0),$B2-TIME(8,30,0))+IF<font color="#008000">($C2<time<font color="#0000FF">(12,0,0)</time,TIME(12,0,0)-$C2),IF($B2>TIME(13,0,0),$B2-TIME(13,0,0))+IF<font color="#008000">($C2<time<font color="#0000FF">(17,30,0)</time,TIME(17,30,0)-$C2)) <tbody> </tbody>

<tbody>
</tbody>

 

[Vanda_a]

See if this is what you're looking for?

	A	B	C	D
1		Clock In	Clock Out
2	Alex	8:29	11:59	0:01
3	Alex	13:01	17:30	0:01
4	John	8:31	11:59	0:02
5	John	13:00	16:59	0:31

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =IF(ISEVEN(ROW()),IF($B2>TIME(8,30,0),$B2-TIME(8,30,0))+IF<font color="#008000">($C2<time<font color="#0000FF">(12,0,0)(12,0,0)</time-$C2),IF($B2>TIME(13,0,0),$B2-TIME(13,0,0))+IF<font color="#008000">($C2<time<font color="#0000FF">(17,30,0)(17,30,0)</time-$C2)) <tbody> </tbody>

<tbody>
</tbody>

I have problem with this (12,0,0)"-$C2)". The formula you typed contain error

Many thanks

 

[Vanda_a]

this one?<time(12,0,0),time(12,0,0)-$c3),if($b3><time(17,30,0),time(17,30,0)-$c3))< html="">

=IF(ISEVEN(ROW()),IF($B3>TIME(8,30,0)
,$B3-TIME(8,30,0))+IF($C3<time(12,0,0)
,TIME(12,0,0)-$C3),IF($B3>TIME(13,0,0),
$B3-TIME(13,0,0))+IF($C3<time(17,30,0)
,TIME(17,30,0)-$C3))<time(12,0,0),time(12,0,0)-$c3),if($b3><time(17,30,0),time(17,30,0)-$c3))[ code]

</time(17,30,0),time(17,30,0)-$c3))[></time(12,0,0),time(12,0,0)-$c3),if($b3></time(17,30,0)
</time(12,0,0)
</time(17,30,0),time(17,30,0)-$c3))<></time(12,0,0),time(12,0,0)-$c3),if($b3>

 

[exceldevs]

Hi Vanda_a,

You're right, for some reason some characters get trimmed off after I save the post. Try this.

=IF(ISEVEN(ROW()),IF($B2 > TIME(8,30,0),$B2-TIME(8,30,0))+IF($C2 < TIME(12,0,0),TIME(12,0,0)-$C2),IF($B2 > TIME(13,0,0),$B2-TIME(13,0,0))+IF($C2 < TIME(17,30,0),TIME(17,30,0)-$C2))

 

[Vanda_a]

Hi Vanda_a,

You're right, for some reason some characters get trimmed off after I save the post. Try this.

=IF(ISEVEN(ROW()),IF($B2 > TIME(8,30,0),$B2-TIME(8,30,0))+IF($C2 < TIME(12,0,0),TIME(12,0,0)-$C2),IF($B2 > TIME(13,0,0),$B2-TIME(13,0,0))+IF($C2 < TIME(17,30,0),TIME(17,30,0)-$C2))

Yeah. It works great. many many thanks

 

[exceldevs]

You're welcome, thanks for the feedback.

 

[Vanda_a]

You're welcome, thanks for the feedback.

Hello. I just got an other problem. some values return this "##############". however I extend the column, still no use.
I can't find the reason why. Please help

Many thanks