+ Reply to Thread
Results 1 to 12 of 12

Average hours per week

  1. #1
    Registered User
    Join Date
    07-26-2014
    Location
    MY-14
    MS-Off Ver
    14
    Posts
    6

    Average hours per week

    Dates filled using formula.
    F shift on weekdays from 10:20 AM – 9:30 PM, weekends 10:30 AM start, 2 hours break.
    M shift on weekdays from 10:20 AM – 6:30 PM, weekends 10:30 AM start, 1 hour break.
    A shift from 1:20 PM – 9:30 PM daily, 1 hour break.
    Weekends are Saturdays and Sundays.

    How to calculate into a single cell?
    Last edited by David_X; 07-30-2014 at 04:02 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Calculate total hours per week with different operation times on weekends

    What days count as weekends, wherever you are?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Calculate total hours per week with different operation times on weekends

    Hi David_X
    Welcome to Excel Forum.

    In future, please attach a sample workbook instead of an image file. As for your request, please see attached workbook (Book11.xlsx). Trust this is what you wanted.
    Good luck

  4. #4
    Registered User
    Join Date
    07-26-2014
    Location
    MY-14
    MS-Off Ver
    14
    Posts
    6

    Re:

    Thanks jewelsharma!

    Incorrect as B1:AF1 uses formula.
    Last edited by David_X; 07-30-2014 at 02:52 AM.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Average hours per week, different times on weekends

    I see you have radically changed Post #4 since this morning!! I think we really need to see a sheet from you, showing what you have got in those cells... B1:Af1

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re:

    I assume it's something like 01/07/2014 in B1 formatted as ddd???

    Can you create a new row 2 using =text(b1,"ddd") in B2 and change the SUMPRODUCT appropriately? That'll work OK.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re:

    Like this, perhaps...
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-26-2014
    Location
    MY-14
    MS-Off Ver
    14
    Posts
    6

    Re:

    Incorrect.
    Last edited by David_X; 07-30-2014 at 02:53 AM.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Average hours per week, different times on weekends

    can you take a look at this one? Can you live with this arrangement?
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Average hours per week, different times on weekends

    Re your PM. gotcha. Easy really. I used your fancy equation to return the day number in row 1, used text(b1,"ddd") to return Mon, Tue, etc in row 2 and now it all works fine.
    Attached Files Attached Files

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Average hours per week, different times on weekends

    Like this?
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-26-2014
    Location
    MY-14
    MS-Off Ver
    14
    Posts
    6

    Re:

    Correct!
    Thank you so much Glenn!
    Last edited by David_X; 07-30-2014 at 02:54 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-02-2014, 01:18 PM
  2. [SOLVED] Calculate total hours per week per schedule
    By rizmomin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2013, 11:28 PM
  3. Replies: 2
    Last Post: 10-24-2012, 02:37 PM
  4. Replies: 15
    Last Post: 05-07-2012, 10:52 AM
  5. Calculating total hours between two cells exc weekends
    By finch82 in forum Excel General
    Replies: 3
    Last Post: 10-26-2011, 04:38 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1