Working hours

F

Fredrik

Hi,

Every month we make a list of our employees working hours.
ex.

monday 10.00 - 16.00 = 6h
tuesday 16.00 - 20.00 = 4h

total 10h

how can i calculate the hours together, some employ can have only 30 hours
per week, now calculate by my fingers....sorry for my bad english

Fredrik, Finland
 
R

RagDyer

Day in Column A,
Start time in Column B,
End time in Column C,

Start in Row 2.

Make sure time entries are TRUE XL recognized times.

Daily total in Column D, using this formula:

=MOD(C2-B2,1)

Say weekly total is in D9.
Format D9 to Custom
[h]:mm
And use this formula:

=SUM(D2:D8)
 
×

מיכ×ל (מיקי) ×בידן

With your permission.
The use of MOD is "Handy and Dangerous" at the same time unless the working
time is: 24:00 h.
Your suggested formula returns 0 instead of 24h if the Start & End time are
the same.
The more common formula, in such cases, is therefore: =C2-B2+(C2<=B2)
Micky


RagDyer said:
Day in Column A,
Start time in Column B,
End time in Column C,

Start in Row 2.

Make sure time entries are TRUE XL recognized times.

Daily total in Column D, using this formula:

=MOD(C2-B2,1)

Say weekly total is in D9.
Format D9 to Custom
[h]:mm
And use this formula:

=SUM(D2:D8)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------




Fredrik said:
Hi,

Every month we make a list of our employees working hours.
ex.

monday 10.00 - 16.00 = 6h
tuesday 16.00 - 20.00 = 4h

total 10h

how can i calculate the hours together, some employ can have only 30 hours
per week, now calculate by my fingers....sorry for my bad english

Fredrik, Finland


.
 
M

Mario

test
????? (????) ????? said:
With your permission.
The use of MOD is "Handy and Dangerous" at the same time unless the
working
time is: 24:00 h.
Your suggested formula returns 0 instead of 24h if the Start & End time
are
the same.
The more common formula, in such cases, is therefore: =C2-B2+(C2<=B2)
Micky


RagDyer said:
Day in Column A,
Start time in Column B,
End time in Column C,

Start in Row 2.

Make sure time entries are TRUE XL recognized times.

Daily total in Column D, using this formula:

=MOD(C2-B2,1)

Say weekly total is in D9.
Format D9 to Custom
[h]:mm
And use this formula:

=SUM(D2:D8)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------




Fredrik said:
Hi,

Every month we make a list of our employees working hours.
ex.

monday 10.00 - 16.00 = 6h
tuesday 16.00 - 20.00 = 4h

total 10h

how can i calculate the hours together, some employ can have only 30
hours
per week, now calculate by my fingers....sorry for my bad english

Fredrik, Finland


.
 
R

RagDyer

Each formula has its shortcomings.

When you copy the formulas down Column D to prepare the form for future use,
your suggested formula calculates 24 hours for those blank rows.
So, you must add to your formula with something like:

=(C2-B2+(C2<=B2))*OR(B2>0,C2>0)
OR
=(C2-B2+(C2<=B2))*AND(B2>0,C2>0)
OR
=IF(AND(B2>0,C2>0),C2-B2+(C2<=B2),0)

to keep the timesheet in some sort of presentable display form.

Of course, you could wait to copy down the formula until you fill in the
individual daily times, but that just adds to the workload.

I would venture to say that the probability of a 24 hour workday is rather
slim to none, since it's illegal in most venues.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
????? (????) ????? said:
With your permission.
The use of MOD is "Handy and Dangerous" at the same time unless the
working
time is: 24:00 h.
Your suggested formula returns 0 instead of 24h if the Start & End time
are
the same.
The more common formula, in such cases, is therefore: =C2-B2+(C2<=B2)
Micky


RagDyer said:
Day in Column A,
Start time in Column B,
End time in Column C,

Start in Row 2.

Make sure time entries are TRUE XL recognized times.

Daily total in Column D, using this formula:

=MOD(C2-B2,1)

Say weekly total is in D9.
Format D9 to Custom
[h]:mm
And use this formula:

=SUM(D2:D8)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------




Fredrik said:
Hi,

Every month we make a list of our employees working hours.
ex.

monday 10.00 - 16.00 = 6h
tuesday 16.00 - 20.00 = 4h

total 10h

how can i calculate the hours together, some employ can have only 30
hours
per week, now calculate by my fingers....sorry for my bad english

Fredrik, Finland


.
 
D

David Biddulph

Put the times in as times, which means using a colon instead of a decimal
point. 10:00, not 10.00
To subtract 10:00 from 16:00, use the minus sign. =B2-A2
To add times which might be more than 24 hours, format as [h]:mm. The
square brackets stop the time wrapping round at 24 hours.
To convert from time to decimal hours, multiply by 24 and format as number
or general, rather than as time.

If, of course, you are going to put 10:30 in as 10.50, then you just treat
everything as number of hours and don't need to multiply by 24.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top