About Time (Question Updated)

arnapsui · Jun 15, 2011

I have submitted the following issue and I got the VBA coding as shown below.

I have exported some data from a Time Attendance software into Excel. There, I have got the data in columnC the times registered by employees as follows:

C1: 08:10 12:10 13:15 20:10 -->(this all in one cell)
C2: 07:59 12:09 12:59 20:59 -->(this all in one cell)
C3: 09:40 13:20 -->(this all in one cell)

The company have two shifts per day and one day-off. I want to calculate the total time the employee worked per day.

[FONT=&quot]Sub[/FONT][FONT=&quot] MG01Mar42[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] ray As Variant[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] tot As Date[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] Rng As Range, Dn As Range[/FONT]
[FONT=&quot]Set[/FONT][FONT=&quot] Rng = Range(Range("C1"), Range("C" & rows.Count).End(xlUp))[/FONT]
[FONT=&quot] For Each Dn In Rng[/FONT]
[FONT=&quot] ray = Split(Dn, " ")[/FONT]
[FONT=&quot] Select Case UBound(ray)[/FONT]
[FONT=&quot] Case Is = 1: tot = TimeValue(ray(1)) - TimeValue(ray(0))[/FONT]
[FONT=&quot] Case Is = 3: tot = TimeValue(ray(1)) - TimeValue(ray(0)) + TimeValue(ray(3)) - TimeValue(ray(2))[/FONT]
[FONT=&quot] End Select[/FONT]
[FONT=&quot] Dn.Offset(, 1) = Format(tot, "hh:mm:ss")[/FONT]
[FONT=&quot] Next Dn[/FONT]
[FONT=&quot]End[/FONT][FONT=&quot] Sub[/FONT]

When I run this coding, I have a problem for some employees as follow:
<table class="MsoTableGrid" style="border-collapse:collapse;border:none;mso-border-alt:solid black .5pt; mso-border-themecolor:text1;mso-yfti-tbllook:1184;mso-table-lspace:9.0pt; margin-left:6.75pt;mso-table-rspace:9.0pt;margin-right:6.75pt;mso-table-anchor-vertical: paragraph;mso-table-anchor-horizontal:margin;mso-table-left:left;mso-table-top: 36.45pt;mso-padding-alt:0in 5.4pt 0in 5.4pt" align="left" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:178.05pt;border:solid black 1.0pt; mso-border-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> Column B
</td> <td style="width:178.1pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> Column C
</td> <td style="width:178.1pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> Column D
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:178.05pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> Employee Name
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> Time In & Out
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> Total Time
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:178.05pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> X
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> 08:10 12:30 16:05
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237">
</td> </tr> <tr style="mso-yfti-irow:3"> <td style="width:178.05pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> Y
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237"> 08:10 12:30 16:05 20:20
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt" valign="top" width="237">
</td> </tr> <tr style="mso-yfti-irow:4;height:6.25pt"> <td style="width:178.05pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt;height:6.25pt" valign="top" width="237"> Z
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt;height:6.25pt" valign="top" width="237"> 12:10 16:05 20:20
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt;height:6.25pt" valign="top" width="237">
</td> </tr> <tr style="mso-yfti-irow:5;mso-yfti-lastrow:yes;height:6.25pt"> <td style="width:178.05pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt;height:6.25pt" valign="top" width="237"> P
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt;height:6.25pt" valign="top" width="237">
</td> <td style="width:178.1pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0in 5.4pt 0in 5.4pt;height:6.25pt" valign="top" width="237">
</td> </tr> </tbody></table>

Employee X has forget to Time-Out at 20:00, so his total hours should be 12:30 – 18:10, but I get wrong total hours
Employee Y is OK in time-in and time-out
Employee Z forget to time-in at 08:00, so his total hours should be 20:20-16:05, but I get wrong total hours
Employee P did not time-in and time-out, so his total hours should be 0, but I get wrong total hours

About Time (Question Updated)

arnapsui

Board Regular

Excel Facts

Similar threads

Forum statistics

Share this page

About Time (Question Updated)

arnapsui

Board Regular

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock