Subscribe
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
Formula
(19 posts) (4 voices)-
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.Posted 3 months ago # -
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!Posted 3 months ago # -
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
Posted 3 months ago # -
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
Posted 3 months ago # -
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
Posted 3 months ago # -
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
Posted 3 months ago # -
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.
Posted 3 months ago # -
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
Posted 3 months ago # -
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
Posted 3 months ago # -
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
Posted 3 months ago # -
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
Posted 3 months ago # -
Hi ,
You are too kind , thanks for the appreciation. Keep visiting.
Narayan
Posted 3 months ago #
Reply
You must log in to post.
