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
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
What does any of that mean?
Entia non sunt multiplicanda sine necessitate
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.
In C2,
=($A2<=C$1) * (($B2>=C$1) + ($B2=""))
Last edited by shg; 05-26-2015 at 04:02 PM.
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"
Looks to me like that what it does:
Row\Col A B C D E F G H I J K L M N 1START 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
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.
<---------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
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.
Post a workbook that demonstrates the problem.
see attached
Maybe one of these
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
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.
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.
I would still like it to dispaly a "1" for every date <= to the 31 Jul
In C2,
=($A2<=C$1) * ((EOMONTH($B2, 0)>=C$1) + ($B2=""))
Perfect thank you for your help shg
Great it works thank you so much for your help
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks