how do I create a formula to add up time on a time sheet

I am creating a weekly time card for our office.  I have columns for start 
and stop times and have successfully found the formula to get each time 
segment to add up in the third column.  When I try to make that 3rd column 
add up the total hours for the week I can't get a sensible result.
Also, is there a simple way to have my time results automatically subtract 
1/2 hour (for lunch) or do I need to split the day into morning and afternoon 
as I have so far?
Any help will be appreciated.
0
Utf
2/17/2010 9:37:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1965 Views

Similar Articles

[PageSpeed] 38

JR Crawdad wrote:
> I am creating a weekly time card for our office.  I have columns for start 
> and stop times and have successfully found the formula to get each time 
> segment to add up in the third column.  When I try to make that 3rd column 
> add up the total hours for the week I can't get a sensible result.
> Also, is there a simple way to have my time results automatically subtract 
> 1/2 hour (for lunch) or do I need to split the day into morning and afternoon 
> as I have so far?
> Any help will be appreciated.


Some help here:

http://www.cpearson.com/excel/overtime.htm
0
Glenn
2/17/2010 9:48:19 PM
See Chip Pearson's site for Timesheet calculations, including OT and lunch
breaks.

http://www.cpearson.com/excel/overtime.htm

And for more help doing Time calcualtions see this site of Chip's

http://www.cpearson.com/excel/datetime.htm#AddingTimes


Gord Dibben  MS Excel MVP


On Wed, 17 Feb 2010 13:37:01 -0800, JR Crawdad <JR
Crawdad@discussions.microsoft.com> wrote:

>I am creating a weekly time card for our office.  I have columns for start 
>and stop times and have successfully found the formula to get each time 
>segment to add up in the third column.  When I try to make that 3rd column 
>add up the total hours for the week I can't get a sensible result.
>Also, is there a simple way to have my time results automatically subtract 
>1/2 hour (for lunch) or do I need to split the day into morning and afternoon 
>as I have so far?
>Any help will be appreciated.

0
Gord
2/17/2010 10:08:14 PM
The best way to get help is to tell us how your data is laid out, and what 
formulas you are using, what results you are getting, and what you want 
instead. Without that, we're just guessing.

Assuming you have start time in column A, and stop time in column B, the 
your work time would be:
=b1-a1
formatted as a time.
If you want to subtract 1/2 hour for lunch, do it here, as in:
=b1-a1-time(0,30,0)

To get your weekly total, presumably you're summing times, like:
=sum(c1:c7)

The format you want for this is likely: [hh]:mm
The square brackets stop Excel from rolling over after 24 hours.

Regards,
Fred

"JR Crawdad" <JR Crawdad@discussions.microsoft.com> wrote in message 
news:43210447-71BB-4C21-97A1-2106DFF07727@microsoft.com...
>I am creating a weekly time card for our office.  I have columns for start
> and stop times and have successfully found the formula to get each time
> segment to add up in the third column.  When I try to make that 3rd column
> add up the total hours for the week I can't get a sensible result.
> Also, is there a simple way to have my time results automatically subtract
> 1/2 hour (for lunch) or do I need to split the day into morning and 
> afternoon
> as I have so far?
> Any help will be appreciated. 

0
Fred
2/17/2010 10:10:30 PM
Reply:

Similar Artilces:

formula tu sum up to a number till 7
This is the data I have: 2 3 4 5 5 6 6 7 7 1 4 5 1 2 I want in column C to G cells to be filled till 7 starting to count from B and then starting again. Result I want: A B C D E F G 2 3 4 5 6 7 1 4 5 5 6 6 7 7 1 4 5 1 2 And so on... How can I do this... Thank you so much!!! I don't fully understand this. Why do you not show 2 as the starting value in column C? Why do you have a 1 after the 7? Is this the corresponding value from column B? Would C2 start with 4 and continue across until you next...

Exchange Migration Wizard and creating new SMTP reply addresses
Hello, I'm migrating from a Netware 6.5 environment, but am going to be using another tool because of the need to migrate archives and personal address books. I would, however, like to use the Microsoft tool to populate the GWISE SMTP address with the old GroupWise address. Is there a way to run the Exchange Migration Wizard in a mode that will only create the additional GWISE SMTP addresses for all of my users? Aaron ...

Can I add a domain to the Blocked Senders list?
I am just getting used to Outlook 2003 and trying to make use of the SP{AM filter. I see that when I right click on a message that one of the options id to add a sender to the "Blocked Senders List" Is there a way that I can also include the option to block a domain? Thanks Dunc Dunc wrote: > Is there a way that I can also include the option to block a domain? There is an easy way, if you're running Outlook 2003+SP1. Dunc wrote: > Is there a way that I can also include the option to block a domain? Right-click on any e-mail, choose (menu) Junk Mail, Junk Mail...

Re: Outlook 2007 create background
I played around with this further. You can still use any HTML editor to = create stationery and place it in the user's Stationery folder. You can = also use FrontPage 2003 to create new themes with background images. = Details at = http://turtleflock-ol2007.spaces.live.com/blog/cns!C1013F1F9A99E3D8!230.e= ntry --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =...

time quick entry
I would like to do quick entry for times to the hundredth of a second (e.g., m:ss.00). I've used Chip Pearson's method for time quick entry as a starting point, and edited the Cases, but it's not working for me. I've included the code I've tried below. Thanks for any help, ~ Horatio Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count > 1 Then Exit Sub End If If Target.Value = "" Then ...

Formula argument based on text
Hi All, I have cells (A1:A10) populated with text ("Pass" or "Fail"). In cell (A11) I want to have a formula that returns the text: "pass" if all the cells (A1:A10) contain the text "pass" "fail" if any one of the cells (A1:A10) contains the text "fail". How would I write the formula to achieve this? Regards gregork In A11 enter: =IF(COUNTIF(A1:A10,"Fail")>0,"Fail","Pass") HTH "gregork" <gregork@paradise.net.nz> wrote in message news:0RXSb.19911$ws.2664893@news02.tsnz.net... > ...

Add item discounting to inventory wizard
It would be infinately useful for me to be able to assign volume discounts to items based on MPQ and Regular, A, B, and C prices. For example. I sell an item that comes in a box quantity of 25. If a customer at any price level buys 25, the system discounts the item 10%. As it is now, I must go through item by item and enter this information. In my situation, I have thousands of items to update every time prices change. So it would be nice to simply say "if [MPQ] of item is purchased, discount 10%". ---------------- This post is a suggestion for Microsoft, and Microsoft r...

Adding Named Formulas to a Chart
Guys I've been trying to figure out how to add a named formula to series' field. Do i just add teh name of the formula in quotes, singl quotes, or what? thanks -- Message posted from http://www.ExcelForum.com Maurice, like this: =workbook.xls!rangename -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "maurices5000 >" <<maurices5000.178a3w@excelforum-nospam.com> wrote in message news:maurices5000.178a3w@excelforum-nospam.com... > Guys I've been trying to figure out how to add a named formula to a > ...

change the formula by changing contents of cell
I have a table that ranks a list of players by their statistics, for example, when I change cell B2 to "walks", my formula (large) lists the player with the most walks in cell B4, 2nd most in B5, 3rd in B6... that works well because more walks are good. But, when I change cell B2 to "strikeouts" I want it to list the player with the fewest number of strikeouts in B4, next fewest in B5 and so on. Is there a way to change the "large" function to the "small" function within the formula? I have set up a helper cell (C2) that changes fro...

Promoting and Outlook Email and Creating Contact
After reading an Email in Outlook I promote it to CRM. The author of the Email is not a CRM contact so their name appears in red. Clicking on the authors name, CRM gives the option of reconciling to another contact or creating a new contact record. When I select a new contact record, why doesn't CRM populate the name and Email address just like when you create a contact from an Email in Outlook? Why should I have to go back and look up the Email address from the Email itself to place in the CRM Contact record? I'd post that as a suggestion for future product enhancements. Ho...

display only one formula
i'm taking a computer test and i need to know how to display my formula when it prints so the formula doesn't calculate, but i only need one of the formulas to be shown the rest need to stay in calculated form. How would i do this? Hi Format the cell as Text, select the cell, press F2, and then Enter keys. -- When sending mail, use address arvil<at>tarkon.ee Arvi Laanemets "norcalchick2207" <norcalchick2207@discussions.microsoft.com> wrote in message news:6F223ADF-4546-44C9-8BC8-6D2825872C10@microsoft.com... > i'm taking a computer test and i need ...

how to move cursor in the formula
Hi, all, The formula is much too long in the conditional formatting and only a portion of it is displayed. How to move the cursor without changing the cell references? thx Try hitting the F2 key. And watch the lower left corner of the Status bar. You'll be toggling between point and Edit mode. It works that way in worksheet formulas, too. Jack Zhong wrote: > > Hi, all, > > The formula is much too long in the conditional formatting and only a > portion of it is displayed. How to move the cursor without changing the > cell references? > > thx -- Dave ...

Why Out of memory error when trying to create a thread?
I'm using MS Visual C++ .NET, Windows XP, and 1GB RAM. Why could be the reason why I get an error message saying Out of memory when running the following code CClientThread* pThread = (CClientThread*) AfxBeginThread(RUNTIME_CLASS(CClientThread), THREAD_PRIORITY_NORMAL, 0, CREATE_SUSPENDED); My application does only have this additional thread. Joachim wrote: > I'm using MS Visual C++ .NET, Windows XP, and 1GB RAM. > Why could be the reason why I get an error message saying > > Out of memory > > when running the following code > > CClientThread* pThread =...

Help what kind of formula?
I've used Excel formulas in a basic way (sums, averages, divisions, and multiplication of cell data) for about a year. I came across this formula today for a project I need to complete. I've never seen the "SUMIF" or the dollar symbol, OR the ampersand! After hilighting the formula to see if I could just figure it out, I'm stumped. Any chance someone could explain what function these symbols serve Here is the formula =SUMIF($D$3:$D$28,"="&$C38,H$3:H$28) :confused: -- Emil0 ------------------------------------------------------------------------ Emil...

create new worksheet based on month change
Hi, I have a problem I hope someone can help me with. I have a worksheet for employees to enter data. At the beginning of each month I run a macro which copies a mastersheet to start the new month. The first column of the sheet is for the employee to enter their name. Using code when they move to the 2nd column it automatically inserts the date and the 3rd column the time both based on whether there is an entry in the first column. I want to call my new worksheet macro when the date changes month. I have been trying to use ActiveCell.Offset command to look at the date in the row above but with...

If formula #3
I am trying to find an if formula that says if the result of A-B is less than ..03 then 0 but if the result is less than 0 (example -.03) than show result. The reasoning may help. We don't want to be bothered with amounts less than 3 cents but we do want to display the credit balances because we have to refund the amount. Another way of trying to explain what I am trying to do is if the result of A-B is either .01,.02,.03, than just make it 0 but if it is -.01 show result as negative. Any help would be appreciated. Try this: =IF(OR(A1-B1={0.01,0.02,0.03}),0,A1-B1) HTH, Paul -- &...

creating shared calendar in outlook 2007
Hi, I would like to create a shared calendar (diary, bookings) for the corporate, the thing is, when I go to organization and default folders, I'm able to create but when I go to OWA or Outlook 2K7 can't seem the folder (either rss and others, only the main mailboxs), also I cannot see public folder , only mailbox in bot owa and outlook 2k7. What is the best method to create a shared calendar? Sorry, I'm completely newbie regarding ex2k7 On 4 Mar 2007 03:50:49 -0800, "k0D" <camocas@gmail.com> wrote: >Hi, > >I would like to create a shared calendar (di...

properly creating Personal Folders
I have purchased Microsoft Office 2003 and in briefly reading the included book, it stated three licences were included. I assume that I am able to create three Persanol File Folders in OutLook. I am hoping to keep organized by creating; one for myself, one fordaughter and another for my mother. If I am not correct, please inform me and if I am correct, would you kindly instruct me on how I create the different Personal File Folders. Thank you File > New > Outlook Data File Your license will not affect how many PST files you can create. You might also want to consider creating...

Can not create mailbox on new volume
Hi, I installed new volume and attached through SCSI to Exchange 2003 server. I created new database on new volume and started to move mailboxes in order to free space. When I try to create new mailbox in new volume it,s fault. i can create account , but not mailbox.i can create on old volume and after to move. I do not know if this is active Directory problems or new HD. Please, help >>When I try to create new mailbox in new volume it,s fault. What does the error say? Is the new store mounted? Vlad wrote: > Hi, > I installed new volume and attached through SCSI to Exchang...

Referencing Sheet Tabs
Excel 2003. I produce a weekly cash report. Each new weekly report refers to several cells on the previous week's sheet. I produce each new sheet by copying the last one and changing its name. I then have to update the formulas so that they look at the now previous sheet. Is there some way to enter the sheet tab name into a formula so that the name of the tab is taken from another cell. Hi captain, Lets say cell A1 contains the sheet name cell A2 contains the row no cell A3 contains the column no of the cell you wish to find. Then =ADDRESS(A2,A3,,,A1) will give you the cell addre...

How can I create and display a chart dynamically on a UserForm wi.
I am trying to create charts dynamically using VBA and Excel. The data being plotted will change frequently, and my client wants to interface only with the user form, and not the worksheet itself. I know that you can use the MSChart control to do this in VB6, but I was wondering if there is a similar control for use with Excel and VBA. If so, any additional info on how to use it would be much appreciated. Thanks. I think it's an incredibly dumb idea to want to interface only with a userform (and sacrifice the flexibility of direct access to a chart). Maybe, you can convince your c...

Time #2
I need to be able to click on a cell and have it be populated with the current HH:MM:SS. I know ctrl+shift+; will do the job but I need it to be even more simple than that for the end users. One way: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With Range("J1") If Not Intersect(Target, .Cells) Is Nothing Then .Value = Time .NumberFormat = "HH:MM:SS" End If End With End Sub ...

Scaling x-axis with time
Can anyone help me with a problem I am having? I have a spreadsheet showing 2000 data points plotted against time, sounds simple However, the number of data points corresponding to 1 second of 'footage' varies. E.g. in a 1 second interval, I sometimes get 5 readings being recorded (0.2, 0.4, 0.6, 0.8, 1.0 sec), and in the next 1 second interval, I get 10 readings being recorded (0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0 sec). When it comes to graphing the data, the time scale along the X-axis is not 'to scale', I am looking to have the tick mark labels to be di...

Charting blocks of time as a bar graph
Is there a way to convert/display a given chunk of time (for example a person's shift) into a bar graph/chart? For example: If someone works 6am-2pm, I would like to have that shift show up as a bar in a graph covering those hours. I would ultimately like to enter a person's shift and have it display somewhere else on the page or another sheet in a graph form to see graphically if we are short in coverage somewhere. An example of what I mean can be found here: https://public.me.com/gadgetman Any suggestions/help???? hi, you can download a model (Gantt Chart) here: http://o...

Vacation/Sick Time and Project Accounting
Is there a way we can automatically update the accrued vacation/sick time in Payroll by entering a non-billable project number in Project Accounting and set the pay code to VACN or SICK in the timesheet entry form? ...