Working hours
in [General]
|
Prev: Formula shows in cell instead of results (text reference)
Next: formula needs to move across 1 column every week
From: Fredrik on 5 Apr 2010 18:33 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
From: RagDyer on 5 Apr 2010 18:58 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" <Fredrik(a)discussions.microsoft.com> wrote in message news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B(a)microsoft.com... > 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 >
From: מיכאל (מיקי) אבידן on 6 Apr 2010 02:37 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" wrote: > 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" <Fredrik(a)discussions.microsoft.com> wrote in message > news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B(a)microsoft.com... > > 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 > > > > > . >
From: Mario on 6 Apr 2010 04:04 test "????? (????) ?????" <micky-a*at*tapuz.co.il> wrote in message news:4FA751F6-C04D-43F4-B968-8093CE2A29FD(a)microsoft.com... > 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" wrote: > >> 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" <Fredrik(a)discussions.microsoft.com> wrote in message >> news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B(a)microsoft.com... >> > 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 >> > >> >> >> . >>
From: RagDyer on 6 Apr 2010 15:07
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 ! --------------------------------------------------------------------------- "????? (????) ?????" <micky-a*at*tapuz.co.il> wrote in message news:4FA751F6-C04D-43F4-B968-8093CE2A29FD(a)microsoft.com... > 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" wrote: > >> 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" <Fredrik(a)discussions.microsoft.com> wrote in message >> news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B(a)microsoft.com... >> > 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 >> > >> >> >> . >> |