About Time

[arnapsui]

Hi

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.

Is there a way to do this?

Thanks

 

[Andrew Poulsom]

You could use Data|Text To Columns with space as the delimiter to put your times in 4 columns.

 

[MickG]

Try this:-
Result times in column "D".

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

Regards Mick