1. ## 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"

Thanks

2. ## Re: Headcount Forecasting

What does any of that mean?

3. ## 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. ## Re: Headcount Forecasting

In C2,

=(\$A2<=C\$1) * ((\$B2>=C\$1) + (\$B2=""))

5. ## 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. ## 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. ## 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:
`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.

8. ## 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. ## Re: Headcount Forecasting

Post a workbook that demonstrates the problem.

see attached

11. ## Re: Headcount Forecasting

Maybe one of these

12. ## 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. ## 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. ## Re: Headcount Forecasting

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

15. ## Re: Headcount Forecasting

In C2,

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

16. ## Re: Headcount Forecasting

Perfect thank you for your help shg

17. ## Re: Headcount Forecasting

Great it works thank you so much for your help

18. ## Re: Headcount Forecasting

You're welcome.

##### Users Browsing this Thread

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

#### 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