• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula

J

jrl1208

Member
Hi


I would like to know if there is a formula that will give pick up the start and end date as per the below table. Thanks.


Start End Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12

Feb-12 Apr-12 1 1 1

Jan-12 Jun-12 1 1 1 1 1 1

Mar-12 Apr-12 1 1
 
Hi, jrl1208!


In row 1: Start (text), End (text), Jan-12 (date, current year), and so on.

In A2:B4, dates.

In B3: =SI(Y($A2<=C$1;$B2>=C$1);1;"") -----> in english: =IF(AND($A2<=C$1,$B2>=C$1),1,"")

Copy B3 to proper range.


Regards!
 
What SirJB7 is telling you is


In Cell C2 type the following formula (I added Month() so you can understand it better).


=IF(AND(MONTH($A2)<=MONTH(C$1),MONTH($B2)>=MONTH(C$1)),1,"")


Now copy that formula in your Table (From C2 to H4).


Regards.
 
Hi Nacky


Thank-you for the clarification.

However I wasn't clear in what I wanted. So let me try explaining it again.


Eg.

Within the cell range I have "1" indicating I have resource for that particular month.

From C2:H2 I have resource starting in Feb12 and ending in Mar12

I want to know if there is a formula that picks up the first "1" and put the start Month in cell A2 and last "1" and puts the month in cell B2.

At the moment I'm manually entering the start and end date and this is time consuming if my I have 300 lines to fill.

your help is much appreciated.
 
Hi, jrl1208!

Here's the uploaded file:

http://www.4shared.com/file/Of-4TeT6/Formula__for_jrl1208_at_chando.html

Check if it works for you.

Regards!
 
Thank-you SirJb7


My work have block access to your link but I will download the file when I get home.


Thank-you for your help.
 
Hi SirJb7


I've manage to download the file and had a look.


Unfortunately the formula you wrote is not exactly what I want. The formula will spread the "1" across the months but that is not what I want.


Eg. The "1" is already spread in the range C2:H4. I want a formula to identify which month has the 1st "1" and which month has the last "1".


Thanks
 
Hi ,


Will the 1s be in consecutive months , or can there be gaps in between ?


If there are no gaps , then you can do the following :


1. I assume that the month headers are actually dates , which have been configured to display in the "mmm-yy" format.


2. The month headers are in the range C1:N1 , for one year.


3. The start months are in column A , starting from cell A2 , and the end months are in column B , starting from B2. These are also dates , configured to display in the "mmm-yy" format.


Given the above assumptions , put in the following formula in A2 and B2 , and copy downwards :


A2 : =INDEX($C$1:$N$1,MATCH(1,$C2:$N2,0))


B2 : =DATE(YEAR(A2),MONTH(A2)+COUNTIF($C2:$N2,1)-1,1)


Narayan
 
Hi Narayan


Thank-you so much!!! Yes this is the formula I'm after. This will save me so much time. Thank-you once again!!!!!!!!!!!!!


But just out of curiosity, what if "1" is not consecutive, there is gaps in between.

Is there a formula that will do the trick?


jrl1208
 
Hi ,


If there are gaps in between , the problem is that you will have more than one start and stop ; in such a case what do you wish to do ? For example , if there are 1s between March and June , and then between September and November , do you want just March as the start and November as the end ? If so , then yes , formulae can be used , but they will be different from what I've posted earlier. If you want these , I can post them once I am through with them.


Narayan
 
Hi Narayan


Yes I would like a formula to cater for gaps in between months. As per your example, 1s between March and June , and then between September and November, I want March to be the start date and November to be the end date.


Thank-you for your help.
 
Hi ,


In column B , starting with cell B2 , put in the following formula , as an array formula ( entered with CTRL SHIFT ENTER ) :


=DATE(YEAR(A2),LARGE((C$2:N$2=1)*(COLUMN(C$2:N$2)-COLUMN(C$2)+1),1),1)


Copy this down to the remaining relevant cells in column B.


The formulae in column A will remain as they were.


Narayan
 
Hi ,


I forgot to tell you that the formula given in the prior post will work only if all the months fall in the same year ! If they can be spread over more than one year , then use the following formula , in B2 , copying it downwards :


=INDEX($C$1:$U$1,LARGE((C2:U2=1)*(COLUMN(C2:U2)-COLUMN(C2)+1),1))


where the dates range can go beyond December 2012 , as far as you want. This also will be an array formula ( to be entered using CTRL SHIFT ENTER ).


Narayan
 
Hi Narayan


Thank-you...


I came across another problem with my data.

What if the range consist of more than one variable.

Eg "1", "0.5" or "0.25"


I'm so sorry for not picking this up earlier.


cheers

jrl1208
 
Hi ,


No problem ; in the check where =1 is used , make it whatever is required ; if you only need to check for positive values , make it >0 ; if you need to check for non-zero values ( both positive and negative ) , use <>0 ; everything else will remain the same. Only , be aware that the formulae in column B are array formulae , to be entered with CTRL SHIFT ENTER.


The following formula , in column A :


=INDEX($C$1:$N$1,MATCH(1,$C2:$N2,0))


will need to be changed ; I'll get back to you on this.


Narayan
 
Hi ,


To cater to any non-zero value in the table , for the start date , use the following formula , entered as an array formula , using CTRL SHIFT ENTER :


=INDEX($C$1:$U$1,MATCH(1,C2:U2/C2:U2,0))


Dividing any cell by itself will give either a #DIV/0! error , or a 1 ; as long as at least one cell within the table has a non-zero value in it , the MATCH function will find a 1. In this case , the formula itself will work ; nor do you need to check for errors using IFERROR.


Narayan
 
You must log in or register to reply here.
Back
Top