Headcount Forecasting

[Shaun_Chetty]

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

[shg]

What does any of that mean?

[Shaun_Chetty]

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.

[shg]

In C2,

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

[Shaun_Chetty]

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"

[shg]

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

[newdoverman]

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.

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.

[Shaun_Chetty]

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.

[shg]

Post a workbook that demonstrates the problem.

[Shaun_Chetty]

see attached

[popipipo]

Maybe one of these

[shg]

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.

[newdoverman]

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.

[Shaun_Chetty]

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

[shg]

In C2,

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

[Shaun_Chetty]

Perfect thank you for your help shg

[Shaun_Chetty]

Great it works thank you so much for your help

[shg]

You're welcome.