Hi Guest, Welcome to the quilting forums, register now —or—

Page 1 of 2 12 LastLast
Results 1 to 10 of 19
  1. #1
    Missouri Star

    Join Date
    Nov 2012
    Location
    Kitchener, Ontario Canada
    Posts
    4,789
    Post Thanks / Like

    Default more excel help...uhg.

    Ok....i need more excel help.

    In my file I have a sheet for every month. In each month I have each day outlined:

    1-Aug
    2-Aug
    3-Aug

    etc.

    If each work day is comprised of 8 hours (including a one hour lunch) how do I total the number of total hours in a given month that a person is required to work? Does anyone know that formula? The dates are in column C (starting at row 4) but there are blank rows in between. Here is an example of my sheet

    Click image for larger version. 

Name:	time sheet.jpg 
Views:	38 
Size:	81.2 KB 
ID:	134683

    So 8 hour days and in the month 18 work days in the month. How do I get Excel to tally that without me having to do it manually for every sheet I create?

    Thanks for any help!!

  2. #2
    Missouri Star

    Join Date
    Nov 2012
    Location
    Kitchener, Ontario Canada
    Posts
    4,789
    Post Thanks / Like

    Default Re: more excel help...uhg.

    This is the formula my boss tried but the result came up as 00

    =COUNT(C4:C7+C12:C15+C19:C23+C27:C31+C35:C38)*7

  3. #3
    The Guild President

    Join Date
    Sep 2015
    Posts
    816
    Post Thanks / Like

    Default Re: more excel help...uhg.

    I'm not sure if I'm following your question correctly or not. Are you trying to add up the columns that say total working hours? (I'm not sure from looking what column # that is.) If so then I would take the 25.0 hours that you have from week one and continue to do the same for the end of each week. At the bottom I would have a column to add those weekly totals. If you want to take it a step further to verify accuracy then you could add each one of those columns (excluding the total column) to make sure that one comes out the same at the end of the month.

  4. #4
    Missouri Star

    Join Date
    Nov 2012
    Location
    Kitchener, Ontario Canada
    Posts
    4,789
    Post Thanks / Like

    Default Re: more excel help...uhg.

    No. I have that figured out. What i need is the baseline hours for the month. Everyone must work a set number of hours per month. A given day has 7 hours of work time plus an unpaid lunch. That's fine. But we have flex time and people need to know what the base hours for the month are.

    So if the regular work month has 31 days, and each work day spans 8 hours, that is equal to 248 hours less one hour of unpaid lunch per day. So I guess really 31 x 7 hours.

    I want excel to calculate the hours for each month as I update each year of months. Does that make sense?
    Last edited by WendyI; September 2nd, 2016 at 02:26 PM.

  5. #5
    Missouri Star

    Join Date
    Mar 2015
    Location
    Lawrence, Ks
    Posts
    2,177
    Post Thanks / Like

    Default Re: more excel help...uhg.

    Oh Wendy I wish I could help you, but I've never been able to figure Excel out. Hopefully someone will come along who can.

  6. #6
    Missouri Star

    Join Date
    Jan 2016
    Location
    Southern CA
    Posts
    1,139
    Post Thanks / Like

    Default Re: more excel help...uhg.

    Quote Originally Posted by WendyI View Post
    No. I have that figured out. What i need is the baseline hours for the month. Everyone must work a set number of hours per month. A given day has 7 hours of work time plus an unpaid lunch. That's fine. But we have flex time and people need to know what the base hours for the month are.

    So if the regular work month has 31 days, and each work day spans 8 hours, that is equal to 248 hours less one hour of unpaid lunch per day. So I guess really 31 x 7 hours.

    I want excel to calculate the hours for each month as I update each year of months. Does that make sense?
    If everyday is a "work day"... then use one block where you would type in the number of days in that month. In the next block would be the forumula... Ex.. A1 (31) in A2 you would type =A1*7 So everytime you manually change the number of days in a month... the next block would automatically do the multiplication each time too. Does this make sense???

  7. #7
    Missouri Star

    Join Date
    Jan 2016
    Location
    Southern CA
    Posts
    1,139
    Post Thanks / Like

    Default Re: more excel help...uhg.

    I should have added... once you type in the "equal" sign, then put your cursor on the A1 cell and a blue A1 appears in your A2 cell, then add *7...

  8. #8
    The Guild President

    Join Date
    Sep 2015
    Posts
    816
    Post Thanks / Like

    Default Re: more excel help...uhg.

    If I'm following this right then what Mimi Howard says makes sense. This way it will automatically give you a running total.

  9. #9
    Missouri Star

    Join Date
    Jan 2016
    Location
    Southern CA
    Posts
    1,139
    Post Thanks / Like

    Default Re: more excel help...uhg.

    I was doing the math formula for just the total number of hours for that month... do you need to also take that number and then use it to deduct the hours as they are worked by the person... ? Don't want to make it more complicated than needed for sure..

  10. #10
    Missouri Star

    Join Date
    Nov 2012
    Location
    Kitchener, Ontario Canada
    Posts
    4,789
    Post Thanks / Like

    Default Re: more excel help...uhg.

    Thanks! I figured out an easier way! LOL! sometimes you just have to plow in and it works itself out! thanks again everyone xooxoxxoxo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •