+ Reply to Thread
Results 1 to 18 of 18

Headcount Forecasting

  1. #1
    Registered User
    Join Date
    05-26-2015
    Location
    Johannesburg
    MS-Off Ver
    2010
    Posts
    9

    Headcount Forecasting

    hi,

    I am trying to create a formulae to populate the sample based on the start and end dates with "1" and "0"

    Can anyone please help.

    Thanks
    Attached Files Attached Files

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

    Re: Headcount Forecasting

    What does any of that mean?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-26-2015
    Location
    Johannesburg
    MS-Off Ver
    2010
    Posts
    9

    Re: Headcount Forecasting

    As per the attachment i have a start and end date in the first two columns. i am looking for formulae to populate column C to column N. Popoulate with "1" in column c2 if c1 falls between (inclusive) of a1 and a2, etc

    Hope this explains a little better.

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

    Re: Headcount Forecasting

    In C2,

    =($A2<=C$1) * (($B2>=C$1) + ($B2=""))
    Last edited by shg; 05-26-2015 at 04:02 PM.

  5. #5
    Registered User
    Join Date
    05-26-2015
    Location
    Johannesburg
    MS-Off Ver
    2010
    Posts
    9

    Re: Headcount Forecasting

    Thank you.

    It works two questions:
    1. The formulae does not display a "1" in the cell of the end date, ie: if date (colum B) is 23/07/2015 in column I (31/07/2015) it reflects a "0"
    2. What if the end date is blank (column B) i would like it to reflect a "1"

  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,678

    Re: Headcount Forecasting

    Looks to me like that what it does:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    START END
    2015/01/31
    2015/02/28
    2015/03/31
    2015/04/30
    2015/05/31
    2015/06/30
    2015/07/31
    2015/08/31
    2015/09/30
    2015/10/31
    2015/11/30
    2015/12/31
    2
    2015/03/01
    2015/07/31
    0
    0
    1
    1
    1
    1
    1
    0
    0
    0
    0
    0
    3
    2015/02/15
    0
    1
    1
    1
    1
    1
    1
    1
    1
    1
    1
    1

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Headcount Forecasting

    I think that there is an error in the indicated 1 in I2. I think that it should be a 0 as the date in I1 does not fit between start and end. I also entered an x in B3 just so there would be something other than a blank cell and letters come after numbers so would be considered larger than a date.

    This is the formula that I used in C2 to be filled across and down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    All I can say is that it "looks right" as I really don't understand the second row but got results that matched yours.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  8. #8
    Registered User
    Join Date
    05-26-2015
    Location
    Johannesburg
    MS-Off Ver
    2010
    Posts
    9

    Re: Headcount Forecasting

    Hi I have tried all all the options listed above and still dont get the correct answer on my spreadsheet. For some reason i dont get a "1" in the month of the end date:

    Please help as i am not sure what im doing wrong. Ive tried this several times.

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

    Re: Headcount Forecasting

    Post a workbook that demonstrates the problem.

  10. #10
    Registered User
    Join Date
    05-26-2015
    Location
    Johannesburg
    MS-Off Ver
    2010
    Posts
    9

    Re: Headcount Forecasting

    see attached
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,994

    Re: Headcount Forecasting

    Maybe one of these
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

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

    Re: Headcount Forecasting

    Look right to me; the end date (24 Jul) occurs before the column date (31 Jul).

    If that's not the logic you want, please explain clearly what it is.

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Headcount Forecasting

    I pointed out in msg #7 that there was a mistake in I2. The date in I1 is not within the Start and End dates. Either the End date is incorrect or the expected result is incorrect. Change the End date to be equal to or greater than what is in I1 and you will get the 1 that you want.

  14. #14
    Registered User
    Join Date
    05-26-2015
    Location
    Johannesburg
    MS-Off Ver
    2010
    Posts
    9

    Re: Headcount Forecasting

    I would still like it to dispaly a "1" for every date <= to the 31 Jul

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

    Re: Headcount Forecasting

    In C2,

    =($A2<=C$1) * ((EOMONTH($B2, 0)>=C$1) + ($B2=""))

  16. #16
    Registered User
    Join Date
    05-26-2015
    Location
    Johannesburg
    MS-Off Ver
    2010
    Posts
    9

    Re: Headcount Forecasting

    Perfect thank you for your help shg

  17. #17
    Registered User
    Join Date
    05-26-2015
    Location
    Johannesburg
    MS-Off Ver
    2010
    Posts
    9

    Re: Headcount Forecasting

    Great it works thank you so much for your help

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

    Re: Headcount Forecasting

    You're welcome.

+ 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. Headcount Lookup
    By sasha38 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-08-2014, 12:21 PM
  2. Replies: 1
    Last Post: 07-17-2013, 11:34 AM
  3. Employee Headcount per category
    By patrick.warne in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-10-2013, 03:16 AM
  4. Excel 2007 : Headcount unique names
    By daniel_t in forum Excel General
    Replies: 2
    Last Post: 01-31-2011, 07:10 AM
  5. Headcount Within Month
    By som3on3_10 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-25-2009, 03:37 AM

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