+ Reply to Thread
Results 1 to 13 of 13

Need help trying to quantify hours scheduled per hour

  1. #1
    Registered User
    Join Date
    01-18-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    MS Office for MAC 2011
    Posts
    5

    Need help trying to quantify hours scheduled per hour

    I have a schedule that I imported into excel from my business server. I need to find a formula or create one that will give me a breakdown of the number of people scheduled per hour. Their shifts vary throughout the day. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-18-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    MS Office for MAC 2011
    Posts
    5

    Need help trying to quantify hours scheduled per hour

    I have a schedule that I imported into excel from my business server. I need to find a formula or create one that will give me a breakdown of the number of people scheduled per hour. Their shifts vary throughout the day. Any help would be appreciated.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need help trying to quantify hours scheduled per hour

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Glenn



  4. #4
    Registered User
    Join Date
    01-18-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    MS Office for MAC 2011
    Posts
    5

    Re: Need help trying to quantify hours scheduled per hour

    EmployeeSchedule_01192015_3.xlsx

    I think I attached it correctly

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help trying to quantify hours scheduled per hour

    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Need help trying to quantify hours scheduled per hour

    If you rearrange your input data as shown in columns B:D, you could do this:

    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    4
    Employee
    In
    Out
    Time
    On Duty
    Duration
    Employee
    Hours
    5
    Alan
    Mon 01/19/2015 08:00
    Mon 01/19/2015 15:00
    Mon 01/19/2015 08:00
    3
    0:30
    Alan
    52:00
    6
    Barb
    Mon 01/19/2015 08:00
    Mon 01/19/2015 16:00
    Mon 01/19/2015 08:30
    4
    0:30
    Barb
    52:00
    7
    Cain
    Mon 01/19/2015 08:00
    Mon 01/19/2015 16:30
    Mon 01/19/2015 09:00
    5
    0:30
    Cain
    53:00
    8
    Dana
    Mon 01/19/2015 08:30
    Mon 01/19/2015 16:00
    Mon 01/19/2015 09:30
    6
    0:30
    Dana
    50:30
    9
    Eric
    Mon 01/19/2015 09:00
    Mon 01/19/2015 16:00
    Mon 01/19/2015 10:00
    11
    5:00
    Eric
    48:00
    10
    Fran
    Mon 01/19/2015 09:30
    Mon 01/19/2015 16:00
    Mon 01/19/2015 15:00
    9
    1:00
    Fran
    49:00
    11
    Gary
    Mon 01/19/2015 10:00
    Mon 01/19/2015 15:00
    Mon 01/19/2015 16:00
    8
    0:30
    Gary
    44:00
    12
    Hana
    Mon 01/19/2015 10:00
    Mon 01/19/2015 16:00
    Mon 01/19/2015 16:30
    7
    0:30
    Hana
    43:00
    13
    Ivan
    Mon 01/19/2015 10:00
    Mon 01/19/2015 18:00
    Mon 01/19/2015 17:00
    9
    1:00
    Ivan
    45:00
    14
    Jane
    Mon 01/19/2015 10:00
    Mon 01/19/2015 16:00
    Mon 01/19/2015 18:00
    8
    3:00
    Jane
    45:00
    15
    Kent
    Mon 01/19/2015 10:00
    Mon 01/19/2015 16:00
    Mon 01/19/2015 21:00
    5
    2:00
    Kent
    40:00
    16
    Leah
    Mon 01/19/2015 16:00
    Mon 01/19/2015 23:00
    Mon 01/19/2015 23:00
    0
    9:00
    Leah
    39:30
    17
    Mark
    Mon 01/19/2015 16:00
    Mon 01/19/2015 23:00
    Tue 01/20/2015 08:00
    3
    0:30
    Mark
    34:00
    18
    Nina
    Mon 01/19/2015 16:00
    Mon 01/19/2015 21:00
    Tue 01/20/2015 08:30
    4
    0:30
    Nina
    32:00
    19
    Otto
    Mon 01/19/2015 16:00
    Mon 01/19/2015 21:00
    Tue 01/20/2015 09:00
    5
    0:30
    Otto
    32:30
    20
    Peri
    Mon 01/19/2015 16:00
    Mon 01/19/2015 21:00
    Tue 01/20/2015 09:30
    6
    0:30
    Peri
    27:30
    21
    Quin
    Mon 01/19/2015 16:00
    Mon 01/19/2015 23:00
    Tue 01/20/2015 10:00
    10
    5:00
    Quin
    29:00
    22
    Rene
    Mon 01/19/2015 17:00
    Mon 01/19/2015 23:00
    Tue 01/20/2015 15:00
    8
    1:00
    Rene
    12:00
    23
    Seth
    Mon 01/19/2015 17:00
    Mon 01/19/2015 23:00
    Tue 01/20/2015 16:00
    6
    0:30
    Seth
    6:00
    24
    Alan
    Tue 01/20/2015 08:00
    Tue 01/20/2015 16:00
    Tue 01/20/2015 16:30
    7
    0:30
    25
    Barb
    Tue 01/20/2015 08:00
    Tue 01/20/2015 16:00
    Tue 01/20/2015 17:00
    7
    4:00
    26
    Cain
    Tue 01/20/2015 08:00
    Tue 01/20/2015 16:00
    Tue 01/20/2015 21:00
    6
    0:30
    27
    Dana
    Tue 01/20/2015 08:30
    Tue 01/20/2015 16:00
    Tue 01/20/2015 21:30
    5
    0:30
    28
    Eric
    Tue 01/20/2015 09:00
    Tue 01/20/2015 16:30
    Tue 01/20/2015 22:00
    4
    1:00
    29
    Fran
    Tue 01/20/2015 09:30
    Tue 01/20/2015 16:00
    Tue 01/20/2015 23:00
    0
    9:00
    30
    Gary
    Tue 01/20/2015 10:00
    Tue 01/20/2015 15:00
    Wed 01/21/2015 08:00
    3
    0:30
    31
    Hana
    Tue 01/20/2015 10:00
    Tue 01/20/2015 17:00
    Wed 01/21/2015 08:30
    4
    0:30
    32
    Ivan
    Tue 01/20/2015 10:00
    Tue 01/20/2015 16:00
    Wed 01/21/2015 09:00
    6
    1:00


    See the last tab in the workbook at https://app.box.com/shared/97plvyn4ie
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Need help trying to quantify hours scheduled per hour

    deleted -- threads merged.
    Last edited by shg; 01-18-2015 at 04:15 PM.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need help trying to quantify hours scheduled per hour

    I rearraged things a bit. can you use this at all?
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Need help trying to quantify hours scheduled per hour

    Firsttimemanager, please do not post duplicate threads. If this was due to a forum hiccup, then please disregard this. Otherwise, please wiat for members to respond

    I will merge your 2 threads
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Registered User
    Join Date
    01-18-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    MS Office for MAC 2011
    Posts
    5

    Re: Need help trying to quantify hours scheduled per hour

    SHG, That would help me to get the numbers as I make the schedule, but as the staff changes/calls out/gets sent home early/etc. I need an easy quick way visualize this and have my staff understand without having to update this in excel. the more basic the better.

  11. #11
    Registered User
    Join Date
    01-18-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    MS Office for MAC 2011
    Posts
    5

    Re: Need help trying to quantify hours scheduled per hour

    Glenn Kennedy, That is great. Is there any way to have the time standardized instead of military? Also what was the technique you used to get this?

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need help trying to quantify hours scheduled per hour

    Mmmm. I always use military time. It's unambiguous. To enter as such, just enter hh then colon ( : ) then mm.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need help trying to quantify hours scheduled per hour

    Of course, if you prefer AM-PM format, simply format the cells to hh:mm AM/PM as in the attached. No change to the formulas is needed, as all the formatting is doing is changing the way you see the time, and not changing its underlying value.
    Attached Files Attached Files

+ 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. Distribute the hours , to a new table hour by hour.
    By turist in forum Excel General
    Replies: 1
    Last Post: 12-02-2013, 09:47 AM
  2. Replies: 0
    Last Post: 02-13-2012, 10:13 PM
  3. scheduled hours
    By robert220 in forum Excel General
    Replies: 6
    Last Post: 07-29-2008, 07:45 PM
  4. scheduled hours total
    By Jeff Desruisseaux in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-01-2005, 07:05 AM

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