#### Working hours

```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

```
 0
Utf
4/5/2010 10:33:09 PM
excel.misc 78881 articles. 5 followers.

5 Replies
778 Views

Similar Articles

[PageSpeed] 6

```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@discussions.microsoft.com> wrote in message
news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B@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
>

```
 0
RagDyer
4/5/2010 10:58:56 PM
```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@discussions.microsoft.com> wrote in message
> news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B@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
> >
>
>
> .
>
```
 0
Utf
4/6/2010 6:37:01 AM
```test
"????? (????) ?????" <micky-a*at*tapuz.co.il> wrote in message
news:4FA751F6-C04D-43F4-B968-8093CE2A29FD@microsoft.com...
> 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@discussions.microsoft.com> wrote in message
>> news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B@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
>> >
>>
>>
>> .
>>

```
 0
Mario
4/6/2010 8:04:54 AM
```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.

=(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

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@microsoft.com...
> 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@discussions.microsoft.com> wrote in message
>> news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B@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
>> >
>>
>>
>> .
>>

```
 0
RagDyer
4/6/2010 7:07:27 PM
```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.
--
David Biddulph

"Fredrik" <Fredrik@discussions.microsoft.com> wrote in message
news:0D8E9E08-3C90-4DCE-B72E-0779975ECA6B@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
>

```
 0
David
4/6/2010 7:13:27 PM

Similar Artilces:

Can't get bar code font to work in EXCEL 2003
I have a simple formula that converts a part number to a 3 of 9 bar code font in Excel 2003. Works great on my computer but a co-worker, who also has Excel 2003, can't get this to work. On her PC it show the 3 of 9 font in the drop down box but in the cell it is not converting it to a bar code image. Any suggestions? Thanks, Tony Most likely the font is not installed properly... She needs to reinstall the font. It can be done through Control Panel -> Fonts if you have the font available. "Tony" wrote: > I have a simple formula that conve...

OWA stopped working on one of the Cluster Nodes
Our environment has 2 FE servers and an A/A/P cluster. Recently (not sure how long ago) OWA stopped allowing logons on one of the BE nodes. I have checked the configuration for the HTTP resources and they all appear to be identical. When I move the EVS from the Active to Passive node OWA starts working properly. I had installed Symantec's SMSMSE 4.6 around the same time frame and have since removed that. I have tried to Restore the IIS configuration to an earlier date and that fixed the Passive Node but not the Active node. I have removed Exchange and reinstalled and removed IIS and...

Record Macro Relative does not work?
Can someone help me with this macro. I want to go to any cell of my choice and press f2 to edit, then go to the fifth character of that cell, then select the next 6 characters and change them to a smaller font size (8), then "enter". I tried to make this macro using the record macro function, but it did not work how I want it to. It ends up pasting the contents of the cell which I used to record the macro initially. Can this kind of macro be done? To get a relative recorded macro, make sure the stopr recording toolbar is visible, it has 2 icons, one is to s...

I've got an Outlook 2003 client and we are trying to setup a rule to emulate the Out of Office Assistant. I've setup a rule that is supposed to send reply with a template if the sender is from a list of contacts that I've chosen. It isn't working as expected. The first time I tried it, I entered only one (internet) email address which worked perfectly. If I add any addresses to that list, it doesn't work anymore - the reply is not sent. I know that it can reply to internet email addresses, since it worked that one time, but I can't figure out why it won't work when...

Why doesn't the page preview have the same order as work page?
Why doesn't the preveiw page look like the one I built on the work pages. I have "saved as" and "saved. The nagagation bars in preview goes to different pages than titled. HELP!!!!!! ...

Double Click on file and mouse not working
Good afternoon, thanks for taking time to look at my problem. I am trying to open a word document by double clicking on the file name in "my documents". Word opens, but not the document. If you go into File and Open, you can open the document, but the mouse cursor will not go anywhere in the document. I have tried to open Word in safe mode, blasted the normal.dot, copied a normal.dot from another computer, heck I tried disassociating doc from Word. Nothing worked. I even installed Windows Live so I could uninstall it. I'm hoping someone has fixed this problem. ...

DCount?? not working. Pls help
My data is orgnaised as follows: A B C D E David 2245 abcd efgh ijk Susan 2249 xyz etc etc Mickey 1234 etc 123 etc Arnold 4321 etc.... David 1441 Mickey abcd etc etc I want to count the number of non blank cells in B for each "David", "Susan" etc in A, and have the value returned in another sheet. I tried DCount, but it doesnt seem to be working. -- KDDXB I mocked up your data as you provided it, with names in A1:A6 etc. I entered "David" in A8 and this formul...

Can't get Date Range to work from Form to Report
Hi, I've followed Allen Browne's directions very closely for creating a form for a date range to limit results to that date range on a report and can not get it to work for some reason. I've read many threads on this board which all say the same thing and it seems to work for everyone else. :( Here's what I have: A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. The form is called "frmDateRange" and the report is called "Sales Activity Report". The two unbound text boxes are set to "Short Date" format which the user ...

SendTo PDF Not Working on GP 10
I upgraded a client from GP 8.0 to GP 10.0. Since the ugprade, they are unable to use the SendTo PDF option from within GP. We have tried reinstalling Adobe and that hasn't changed anything. The users can see the PDF being created but a blank email doesn't open from their email client as it used to. The client is on version 10.00.0991 and I thought this issue was to be resolved in 10.0 SP 1 but obviously it hasn't been for us. The client is also using Outlook. Is this still a bug with 10.0 or are we missing something? I am on Adobe Acrobat Standard 8.0 with Microsoft ...

Insert list of figures does not work for Pictures
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I can generate a list of all of my inserted objects by caption: Figure, Example, Table, Song, Chart. But when I try to generate a list of the pictures I have inserted (caption reads Picture ...), I get the following message: Error! No table of figures entries found. It's as if the macro that reads the caption list doesn't have Picture in its internal dictionary. I do have the Picture captions in a text box that is formatted as "tight" to allow text-wraparound; could that be causing the problem? If so h...

Money Stopped Working Problem Solved! (or at least explained)
After the SP1 update my Money Plus Deluxe on Vista Ultimate would "stop working" when I selected Portfolio or Help. It turns out that the problem was a conflict with my Roboform program which is a password fill-in program that attaches itself to web pages in order to automatically enter my personal information. Because Portfolio and Help look like web pages to Roboform, it tries to attach and apparently crashes Money. If I stop Roboform before starting Money all is well. Alternatively, I can start Money "as administrator" and that will work too. In microsoft.publi...

Setting up email to work
I created a webpage for my company and when I created the send button I went into Format Form Properties and selected Submit and then went to Form Properties. I then selected Send Data to me in Email and entered in a valid Email. However when I go to test the web page and I enter in the required information and I click Send I get a The Page Cannot Be Displayed error and HTTP 405 - Resource not allowed. What do I do? Reference: "Publisher web publication forms 101": http://msmvps.com/blogs/dbartosik/archive/2006/01/07/80564.aspx DavidF "Russ" <Russ@discussions.mic...

Function for convertion of leters doesn't work.
I'm using function LOWER to convert string i my table on small size but Access gives me a message that the function is not defined.Please, do you know another function? MS Access does not have a LOWER function. Try LCase() instead. -- Cheryl Fischer Law/Sys Associates Houston, TX "Stenli" <svet2000@seznam.cz> wrote in message news:10b201c3626e\$b613a050\$a301280a@phx.gbl... > I'm using function LOWER to convert string i my table on > small size but Access gives me a message that the function > is not defined.Please, do you know another function? ...

Coments on a Work Order
Hello I have been using the system for almost 2 years, and the following error, which began after I install SP 1.3, is driving me crazy. I sell items that require serial numbers. I had added the term "S/N:" on SubDescription 1 for some items. This is where my problem comes to life, Ex. I create a work order for a client, add some items to it, and add the serial numbers. I save it, so I can do something else. After recalling the Work order, none of the coments (not even the S/N) are on it. I have check the Configuration \ Options on the manager and it seems to be ok. Any sugge...

Linking items GREATER THAN O on another worksheet in the same Work
I have a workbook that has about four worksheets … I want to create a list of items that will automatically be included on worksheet tab 4 from Worksheet tab 1 if the quantity for the item listed on the first worksheet has been changed from 0 to any other number (i.e. 1 or 10 etc). I have a list of equipment with pricing on worksheet tab 1 and the list is long. Many of the items will have quantities of zero and will not increase as they will not be selected for the project being created. We have additional columns for cost, extended cost, labor hours etc. If the quantity changes for a...

Calculate the total overlapping time of multiple tasks, excluding non-working times
I am developing a tracking calculator for an overall process. Within the overall process, there are 3 steps which are completed. These steps can be completed independently from one another, but can also (sometimes) overlap. I am trying to determine the total time for the overall process (from beginning to end), without counting the duplicated times where the steps are running parallel to one another, or times when some steps may be completed and are waiting for another to start. I have outlined the basic design of the worksheet below, with times in mm/dd/yyyy hh:mm format. S...

Hello What would cause outlook (and outlook express) to not allow the links in my emails to work? They open a browser window, but nothing ever gets loaded. If this were happening only for 1 link, I'd say it was the url, but this happens with text links, button links etc. I have to "view source" for the button links, copy the url and paste it into my browser -- and that works fine. Email links seem to work. Any suggestions? Thank you Terry V Hold curser over link, press shift key, now you can click on link. "Terry V" wrote: > Hello > What would cause ou...

I use an email classification tool (POPfile . . . getpopfile.org) that marks each email header using X-Text-Classification. I then set up rules in Outlook to recognize the header text and move the email to the proper folder. Here is the problem: sometimes Outlook places the email into the correct folder and sometimes it just lets the email pass through into the default folder even though the header reflects the proper X-Text-Classification. Does anyone have any idea why this rule would only work sporadically? Thanks. ...

Some rules don't work
Outlook 2007, XP Many of my rules have stopped working. Not all, but many, if not most. I even tried to delete some of the non-working rules and re-write them. But when I run them with messages already in my inbox, they don't work. I frequently copy my outlook.pst file for use on a laptop when travelling. It seems that some--but not all--stopped working at the time of such a transition. Of course, that does not explain why some won't work right after I make them. Many of the rules are what to do with emails I send and receive from certain people. Any suggest...

CMemFile not working in VC++ 6.0 RELEASE mode
I am using VC++ 6.0 and have a multi-threaded application (exe & multiple DLLs) which seem to be having a CMemFile object getting clobbered when running in release mode. It does not happen in debug mode. When running in the debugger, the calling thread that builds the memory file reports: GetPosition = 0 GetLength = 228 ptr to memfile = aa2238 the method GetTextLineNOF is passed a ptr to CMemFile and reports: GetPosition = 0 GetLength = 228 ptr to memfile = aa2238 Which agrees as expected. ==== However when running in RELEASE mode: the calling thread that builds the memory file rep...