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
8)
--
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
.
