Calculating Time #5

I need to calculate hours to be worked in one cell. This spreadsheet will 
caluclate the hours scheduled for the week, then add them up and mulitply by 
payrate:

        1                 Mon(2)         tue(3)       wed(4)          
thurs(5)            total hours    
A Employee   {8am-1pm)   (9am-3pm)                (5:30pm-11pm)           ???

the question mark marks where i need the formula
0
stephen (112)
3/7/2007 10:54:16 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
576 Views

Similar Articles

[PageSpeed] 47

Hi STephen:

Use two cells for a day the start time and the end time.

You can then do calculations on the time as in 

B2=mon start
C2=mon finish
tue d2,e2
wed f2,g2
thur h2,i2
total j2=c2-b2+e2-d2+g2-f2+i2-h2
you need to format the cell j2 as custom ans [h]:mm this will put the right 
hrs in and not days and hours.
format the cells as time h:mm

time in 


-- 
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"STephen" wrote:

> I need to calculate hours to be worked in one cell. This spreadsheet will 
> caluclate the hours scheduled for the week, then add them up and mulitply by 
> payrate:
> 
>         1                 Mon(2)         tue(3)       wed(4)          
> thurs(5)            total hours    
> A Employee   {8am-1pm)   (9am-3pm)                (5:30pm-11pm)           ???
> 
> the question mark marks where i need the formula
0
3/8/2007 12:23:57 AM
Stephen,

If your work hours are like you posted, (ie (9am-3pm) that is not a very 
easy format for Excel to use.  I would recommend that you have two columns 
per day formated as time, (ie hh:mm) and enter your Start and Finish times 
like 9:00 and 15:00 or 9:00 AM and 3:00 PM, (but notice the space between 
the time and the AM/PM), in each column then you can sum the total for the 
person with the formula:

=SUM(C2-B2,E2-D2,G2-F2,I2-H2)

Custom format the Totals cells as [hh]:mm to stop the hours rolling over 
into day at 24 hours.

I am assuming that you are working Monday - Friday and no shift crosses 
midnight.

Post back if you need further help.


-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"STephen" <STephen@discussions.microsoft.com> wrote in message 
news:7B24DAAE-4CC7-4C03-AA23-ABE15B6CF8FF@microsoft.com...
>I need to calculate hours to be worked in one cell. This spreadsheet will
> caluclate the hours scheduled for the week, then add them up and mulitply 
> by
> payrate:
>
>        1                 Mon(2)         tue(3)       wed(4)
> thurs(5)            total hours
> A Employee   {8am-1pm)   (9am-3pm)                (5:30pm-11pm) 
> ???
>
> the question mark marks where i need the formula 


0
sandymann2 (1054)
3/8/2007 5:15:09 PM
Reply:

Similar Artilces:

Averaging values between two dates/times
Hi All, I have a spreadsheet that contains 4 columns. Column A = start date, Column B = Start Time, Column C = Stop Date, Column D = Stop Time. I have another spreadsheet which contains meteorological data for a given time period. The data in this spreadsheet lists the Date in column A (eg. Jan 1, 2005 to March 1, 2005). Columns C through AB list the time (0:00 to 23:00) corresponding to the date from column A. So the data looks like a large matrix with dates listed in the rows of column A and 24 hour times listed in columns C to AB. Basically, I am looking for a function in Excel or a prog...

Outlook wants to be configured every time you log in
We are using Outlook 2000 in an Exchange 2000 environment. A new user was created in Exchange. Everytime he tries to get into Outlook, it is prompting him for the name of the Exchange server. We type in the information and he's good to go. But the next time he tries to get in, it prompts him for the same info again! I logged in as admin and went thru the setup thinking that would take care of it. But when he logged in, it prompted him for the exchange server name. Can anyone tell me what step(s) I'm missing? Jim Does she have a roaming or local NT profile and is it just ...

rows delete via macro needs too much time
Hello all, I wrote a macro which have to delete some rows in a sheet. The sheet contains many sum formulas. When I start the macro, it work very well but after 10 deletes it needs more time than before and after 20 deletes it needs over 30 seconds for one delete. When I break the macro and do the delete manually, the same happen. Post the code. Also, have you tried turning off screenupdating and calculation whilst it is running, and then putting it back on at the end:- Sub xyz() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Code................ ...

Unhide multiple sheets at the same time
All, I need your help: I have a lot of Exls work book I need to unhide sheets in. Each work book contains 15 sheets out of which 14 of them must be deleted and all of the 14 are hidded sheets. How do I select all hidden sheets and delete at the same time? Thanks you'll. Shouldn't need to unhide first Sub deletehiddensheets() Application.DisplayAlerts = False For i = Sheets.Count To 2 Step -1 If Sheets(i).Visible = False Then Sheets(i).Delete Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "alish" <alish@discussions.micr...

change service account admin in exchange 5.5
ok so... i apparently had this win2k exchange 5.5 server in my office that was set up a long time ago. It was hiding on say domain2 which is trusted with domain1. i nuked the trust between the two, and removed all of domain2 equipment out of my server room, with the exception of one box. this box i removed it from domain2 and added to domain1. then i changed the services to fire up under a user on domain1, since they were on domain2. all services came on so i figured i was set. then one of the three users on this machine informed me they cant pop. i traced the error back to something in ...

I get an error message each time I attempt to open outlook
I get an error message each time I attempt to open outlook that says: "unable to open your default email folders. could not open the item. try again". after pressing try again, i get a message that says would you likd to open your default file system instead Noonah <Noonah@discussions.microsoft.com> wrote: > I get an error message each time I attempt to open outlook that says: > "unable to open your default email folders. could not open the item. > try again". after pressing try again, i get a message that says would > you likd to open your default f...

store.exe 100% cpu useage hotfix (exch5.5)
I'm having the problem described in KB833561, and it says contact MS for the hotfix. I filled out the form, but haven't heard back from them. Does anyone know where I can get this hotfix? Thank you. It will be much easier if you just call them. Hotfix calls are free. -- Ben Winzenz Exchange MVP MessageOne "Marvin P. Winterbottom" <MarvinPWinterbottom@discussions.microsoft.com> wrote in message news:745FA203-CCEB-41FF-B339-CD1FF0D1545E@microsoft.com... > I'm having the problem described in KB833561, and it says contact MS for > the > hotfix. I ...

Question on Connectors. 3 servers
I've confused myself. 3 servers: server1, server2 & server3. Server1 was our original Exchange box. We've outgrown that box and added another, and migrated most accounts to this server: Server2. We had been using NAV for Exchange, but have now purchased MailSweeper. Ran into some problems and decided I need a test server to work out these problems: Server3. I added a test account on the test server. With that account I can send and receive internet email. I did not add a IMC to the test server, so how am I able to "send" using this test account? thanks Hi Jo...

Can i get an alert when real time data changes?
I import real time data (stock quotes) from outside sources (Bloomberg, etc). I have formulas that calculate changes and ratios throughout the day. I want to be notified (through a popup message, or email) when these values reach a certain range. Is that possible Pop-up or email (or sound) would require programmatic support. Depending on your comfort level with VBA, check out the worksheet object's Calculate (or Change) event. On the other hand, conditional formatting would not but all it would do is change the format of specified cells. -- Regards, Tushar Mehta www.tushar-m...

Exchange 5.5 SP4
We have a problem with the TOTAL K and TOTAL ITEMS column in 'Microsoft Exchange Administrator' program. When selecting the "MAILSERVER -> Private Information Store -> Mailbox Resources" mailboxes with a (presumably) larger content as 4Gb are shown as a 0K mailbox but 19,000+ items are held in that mailbox. Does anyone have experienced this and has more info for me? I would like to know whether there is a patch or so (Knowledge Base has an article, KB 285788 but that does not cover my subject). Thanks. Berry "Berry" <Berry@NOMAILSERVER.TLD> wrote ...

How Money calculate Starting Balance
Symptom: Receive the message "Money has calculated a different starting balance than the one you entered" Within the "Balance the account" function of Money, 'Starting balance' = 'opening balance of the account' + 'total of the reconciled transactions of the account'. 'Starting balance' does not always equal to the account balance at the balance date. To calculate the Starting Balance, let's perform the following: 1. Go to Account List. Right click the account name and select See Account Details. Record the Opening Balance (N...

Trying to calculate a commission
I have variable payments for two components to a sales plan: # of customers maintained which pays a rep .05% # of customers added which pays a rep 1% I receive a report each quarter telling me the number of current customers around the world. I have a spreadsheet that has the number of customers by continent at the beginning of the quarter (in column b) and at the end of the quarter (in column c). For instance, if the title "# of customers" is in cell A1: # of customers 1/1/10 4/1/10 North America 15000 16000 South America 500 400 ...

Elapsed Time Macro
I have a worksheet that contains sensitive data. When it is used, some of the users tend to accidentally leave the data displayed and walk away from it, allowing the data to be read by others who pass by. I would like to have a macro or some other means of automatically changing the screen display to a different worksheet (or blank for that matter) after a fixed amount of time without a keystroke - perhaps one minute or so of elapsed time without a keystroke. Is there a practical way to do this? It would be very difficult to implemement, why not just activate the screen saver to lock the...

Daylight Savings Time and IMAP4
I have an Exchange 2000 server on Windows 2000. We are comtemplating manually patching the OS and skipping the Exchange 2000 patch that would cost us $4000. My question is, does anybody know if you get your email from an exchange 2000 server via IMAP4, will the email stamp be off by an hour during the Extended DST period? A related question. I realize for our OWA users their calendars will definitely be off by an hour. Does this mean that the emails they see via OWA will also be off by an hour? Thanks for any help. -- pmac The patch for Exchange is for CDO, which uses hardcoded timezones...

Isinteg on 5.5 store before moving mailboxes to 2003?
Hello, I have been reading the 2003 Deployment guide from MS regarding Exchange server 2003 and what is involved in moving over from 5.5. One thing I am just curious about, as a general rule of thumb - is it recommended that isinteg -test be run on the 5.5 box prior to moving mailboxes over to 2003? As far as I can tell, we do not have any issues with our store, event log is clean and online backups do not show any irregularities. I am just trying to cover as much as possible before we even test this out in a lab environment. Also, our current 5.5 hardware is fairly old (PII 300,...

5.5
I have Exchange 5.5 and have a public folder set up that handles web requests. I want to set it up as a mail form but I seemed to have set it up as a post form. When trying to start a new email, it ends up being a new post rather than a new email. I am new to adminsterating Exchange 5.5, so any help anyone can give would be much appreciated. I figure it is a settign somewhere - I just can't figure out where. Thanks, Joe ...

Repairing Outlook Every Time
Every time I boot up my machine, Outlook freezes upon sending new mail, reading email with attachments, and looking in other folders. I have to run the Office repair utility every time I boot up my machine just to use Outlook. please help. Reply here but direct email is much appreciated. Jeff ...

Feet to Meter Calculation
In my db I want to insert a room size. The sizes I wish to insert come to me in both square meters and square feet. I want to create two fields one showing square feet one showing square meters. I therefore want to be able to input data in to one field (say square feet) and have the corresponding size show in the other field (square meters) and vice versa Can this be done in a db or do I need to create a spreadsheet calculation and insert it in to the db? How is that done? Any replies greatly appreciated! Clive You have to remember that how the data is stored and how it's i...

Calculating Software Maintenance and Support Renewals
We are a software company that sells annual maintenance and support contracts. These contracts are typically purchased in years, however they can also be purchased in month increments - Does anyone know if this system can automate this so the reps don't have to figure out fractions. ...

Get $5 Now
<HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft DHTML Editing Control"> <TITLE></TITLE> </HEAD> <BODY> <P>&nbsp;</P> </BODY> </HTML> ...

vista time problem
hi, since doing a re-install of the vista home premium os on my laptop yesterday i have had to constantly reset the time it goes back 1 hr every hour i have reset the time in the bios ,and made sure the time zone is correct and tried different time servers any help much appreciated -- martyb "marty" <marty@discussions.microsoft.com> wrote in message news:B25E40C0-5DE5-45ED-8A8F-224C3BFBA2B8@microsoft.com... > hi, since doing a re-install of the vista home premium os on my laptop > yesterday i have had to constantly reset the time > it goes back 1 h...

How do I calculate the age of a person?
If I have the date of birth, how do I calulate the age of a person? Thanks Doug Doug, have a look here for how to do it http://www.cpearson.com/excel/datedif.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Doug Mc" <NOSPAMdougandnancymcneill@chilitech.com> wrote in message news:%23w8uKF0TFHA.2812@TK2MSFTNGP09.phx.gbl... > If I have the date of birth, how do I calulate the age of a person? > > Thanks &...

Default snooze reminder time
Does anybody know the reason we cannot change the default snooze reminder time in Outlook 2003 to be something greater than 5 minutes? I searched Google for how to do this and people seemed to agree that that there is no way. As a side note, I don't understand why Microsoft would not allow us to change the default (who would snooze for only 5 minutes for an appointment days from the time I create it and with a setup reminder of >=1 hr? In other words 99+% of my appointments.). This seems a perfect example of why people look to competing open-source applications. If the...

Synchronize #5
I am using Outlook Express V6.00 for news groups. I just can not seem to understand HELP on "synchronization". Is there any other reference on synchronization? What is being synchronized to what? Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://insideoe.tomsterdam.com --� Milly Staples [MVP - Outlook] Post all replies to the grou...

Taking down a domain controller for a short time
Hello, We have three DCs consisting of windows 2003 Ent. I need to relocate one DC to a different building. Would taking one DC down have any issues with user who already logged in? This DC has the FSMO roles. I will take it down for about 15 min. In the past, I never had any issues. Just thought I ask for opinions. I noticed in the past where one DC was down, the workstation would reroute to the other DC for the time server (I was testing the time server back then). Typical environment with Exchange servers, MS SQL, Terminal server, etc. Thanks for your time. Tnt ...