Time Calculation

[Jerome_RA]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]
[TABLE="width: 92"]
<tbody>[TR]
[TD]Clock on Date
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 98"]
<tbody>[TR]
[TD]Clock on Time
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Start of Shift Date
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 119"]
<tbody>[TR]
[TD]Start of Shift Time
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 95"]
<tbody>[TR]
[TD]Minutes Late
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 92"]
<tbody>[TR]
[TD]1/1/2017
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 98"]
<tbody>[TR]
[TD]2:00 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]1/1/2017
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 119"]
<tbody>[TR]
[TD]1:00 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 95"]
<tbody>[TR]
[TD]60
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[TABLE="width: 92"]
<tbody>[TR]
[TD]1/2/2017
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 98"]
<tbody>[TR]
[TD]12:00 AM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]1/1/2017
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 119"]
<tbody>[TR]
[TD]11:50 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 95"]
<tbody>[TR]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[TABLE="width: 92"]
<tbody>[TR]
[TD]1/2/2017
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 98"]
<tbody>[TR]
[TD]11:00 AM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]1/2/2017
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 119"]
<tbody>[TR]
[TD]10:30 AM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 95"]
<tbody>[TR]
[TD]Present
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[TABLE="width: 92"]
<tbody>[TR]
[TD]1/2/2017
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 98"]
<tbody>[TR]
[TD]3:00 AM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]1/2/2017
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 119"]
<tbody>[TR]
[TD]2:00 AM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 95"]
<tbody>[TR]
[TD]Present
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi All, I need help in terms to calculate Minutes Late. This is quite confusing since it will overlap the Dates. So on example 1, Start of Shift of the employee is 1PM on the 1st of January , then his clock on time is 2p m on January 1, so lates minutes is 60. On Example 2. this is where the confusing part is since it will go to PM to AM then will cross Dates. So 11:50PM Start of Shift of January 1 then will clock on at 12am on January 2, this will only be 10 minutes Late. And if all clock on time are before the start of shift, it will just say Present. Any easy formula for this? The result I wan is the Minutes Late. Thanks Guys!

 

[CalcSux78]

Here's a UDF that should do it... It uses the DateDiff vba function.

Excel 2010

	A	B	C	D	E
Clock on Date	Clock on Time	Start of Shift Date	Start of Shift Time	Minutes Late
Present

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]11:50 PM[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]3:00 AM[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=MinutesLate(C2+D2,A2+B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Function MinutesLate(dtStart As Date, dtStop As Date) As Variant
Application.Volatile

If dtStop > dtStart Then
    MinutesLate = DateDiff("n", dtStart, dtStop)
Else
    MinutesLate = "Present"
End If
End Function