+ Reply to Thread
Results 1 to 6 of 6

IF forumla to Work Out Maximum Number Hours People Can Work-If start/finish during a month

  1. #1
    Registered User
    Join Date
    06-17-2014
    Location
    UK
    MS-Off Ver
    2013
    Posts
    49

    IF forumla to Work Out Maximum Number Hours People Can Work-If start/finish during a month

    OK so say I have a shop and I need to forecast the maximum number of hours people can work.

    OK so on the first row in my Excel Sheet I have a list of months 'Jan-15,Feb-15,Mar-15,Apr-15' and so on.....(in reality these are '01/01/15,01/02/15,01/03/15,01/04/15' but they have been formatted as mmm-yy.)

    I have a variety of employees that have worked varying periods for example:

    1. Person A worked 01/01/15-30/04/15
    2. Person B worked 17/02/15-26/04/15
    3. Person C worked 31/01/15-01/03/15
    4. Person D worked 01/01/15-25/01/15

    Each month the total hours available to work is slightly different: Jan 150 hrs, Feb 125 hours, Mar 160hrs, Apr 140 hrs

    I need an automatic formula on an Excel sheet so that I can see that:

    Person A can work Jan: 150, Feb 125, Mar 160, Apr 140
    Person B can work Jan: 0, Feb 49.10 (125hrs*(11/28 days worked in month), Mar 160, Apr 121.33 (140hrs*(26/30 days worked in month))
    Person C can work Jan: 4.83 (150hrs*(1/31 days worked in month)), Feb 125, Mar 5.16 (160hrs*(1/31 days worked in month)), Apr 0
    Person D can work Jan: 120.96 (150hrs*(25/31 days worked in month)), Feb 0, Mar 0, Apr 0

    Is this available to do with a simple IF formula?

    Thanks in advance

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: IF forumla to Work Out Maximum Number Hours People Can Work-If start/finish during a m

    this is all in the past ?
    how will it forecast
    Each month the total hours available to work is slightly different: Jan 150 hrs, Feb 125 hours, Mar 160hrs, Apr 140 hrs
    is that each person is different or all the same ?

    Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    06-17-2014
    Location
    UK
    MS-Off Ver
    2013
    Posts
    49

    Re: IF forumla to Work Out Maximum Number Hours People Can Work-If start/finish during a m

    Uploaded Excel file with expected results in red. At the moment I am having to work out the days worked in the month and multiply it manually but is there a way to do it automatically?
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: IF forumla to Work Out Maximum Number Hours People Can Work-If start/finish during a m

    In the attached find this array formula in D5 filled down and across to G6.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    We differ in the outputs of D5 and E6. I believe if you check there are 15 days in January and 4 days in February. The upload had 17 and 3.

    I used this approach as an alternative to a long formula with multiple comparisons and nested IFs.

    I find it to be more straight forward, but not everyone does. If you would like some explanation let us know.
    Dave

  5. #5
    Registered User
    Join Date
    06-17-2014
    Location
    UK
    MS-Off Ver
    2013
    Posts
    49

    Re: IF forumla to Work Out Maximum Number Hours People Can Work-If start/finish during a m

    Hi FlameRetired thanks very much!-you're right in your calculations.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: IF forumla to Work Out Maximum Number Hours People Can Work-If start/finish during a m

    You're welcome BDBJ1. Thanks for the feedback and the rep.

+ 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: 12
    Last Post: 01-25-2016, 10:53 AM
  2. [SOLVED] Adding work hours based on particular start or finish times
    By RobertM01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2015, 10:28 AM
  3. Replies: 5
    Last Post: 12-17-2013, 12:00 PM
  4. HELP!! How can a forumla work out the amount of people at any time?
    By dhaslett in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2013, 04:26 PM
  5. [SOLVED] How do i calculate work hours from only a start and finish date and time?
    By transitsolutions in forum Excel General
    Replies: 1
    Last Post: 02-28-2013, 03:39 PM
  6. I need to count the number of people who work before a certain time.
    By nickmessick1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-15-2012, 04:02 PM
  7. [SOLVED] Work hours calculating start and end times
    By hiddenupnorth in forum Excel General
    Replies: 6
    Last Post: 04-20-2012, 01:54 PM

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