Finding start & end dates?

H

haw

Board Regular
Joined
Jan 18, 2005
Messages
113
C1 through Z1 contain dates: 9/1/05, 9/8/05, 9/15/05, etc.
C2 through Z10 contain numbers between 0 and 40 (or are empty)

I need a formula in B2 (that I can then copy down through B10) that will:
1) find the first cell containing a positive number in the range C2:Z2
2) return the value from the corresponding cell in row 1, above that cell.

If C1, D1, E1 = 9/1, 9/8, 9/15,
and C2, D2, E2 = 0, 10, 10
Then I need the result to be the value (date) 9/8.

I also need the same type of formula to find the date for the LAST cell containing a number.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi,

Try:



=INDEX($C$1:$Z$1,MATCH(TRUE,(C2:Z2>0),0))
Confirmed with Ctrl + shift + enter.

and

=LOOKUP(9.99999999999999E+307,C2:Z2,$C$1:$Z$1)
normal enter
 
Upvote 0
variation on the theme:

=INDEX($C$1:$Z$1,,MIN(IF($C2:$Z2>0,COLUMN($C2:$Z2)-COLUMN($C$2)+1)))

entered with control + shift +_ enter. change min() to max() for the last entry
 
Upvote 0
A clarification:

My second formula would give you the last position holding ANY number as that is how I interpreted your query. (Different from Paddy)

If that is the right interpretation

=LOOKUP(2,1/(C2:Z2>0),$C$1:$Z$1)

would have been my answer.
 
Upvote 0
All three of those options seem to work fine for finding the END DATE ... but I cannot figure out how to convert them to find the corresponding START DATE. Would still like some help on this.

If my request is not clear, I've pasted below a sample of what the end result I'm looking for. Thanks.
Book1
ABCDEFG
1StartDateEndDate11/711/1411/2111/2812/5
211/14/0511/28/05557
311/21/0512/05/05101010
411/14/0512/05/053333
511/14/0511/21/0533
Sample
 
Upvote 0
The first formula that Fairwinds posted should work for you in A2, confirmed with CTRL+SHIFT+ENTER, alternatively you can use this formula which only requires ENTER

=INDEX($C$1:$Z$1,MATCH(TRUE,INDEX((C2:Z2>0),0),0))

This picks the date associated with the first value above zero, i.e. if zero is shown the date is not picked
 
Upvote 0
"The first formula that Fairwinds posted should work for you in A2,"

& my doesn't ?!?
 
Upvote 0
OK. I've tried all these suggestions ... and it's clear that I'm missing the significance of the CNTL-SHIFT-ENTER comments. For the suggestions that reference CNTL-SHFT-ENTER ... if I just copy/paste the formulas from this thread into a cell in my sheet, I get #Value! errors.

The formulas that say they work with "simple ENTER" I can cut/paste and they work, no problem.

What does "confirmed with CNTL-SHIFT-ENTER" mean?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Need Help - Formula
2
Replies
16
Views
1K
ststern45
S
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
N
  • Question
Need Formula help
Replies
5
Views
358
AhoyNC
A
J
  • Solved
Checking a start and end date against a range of dates
Replies
3
Views
260
Peter_SSs
Peter_SSs
V
  • Question
MAx Date and Time
2
Replies
16
Views
639
awoohaw
awoohaw

Forum statistics

Threads
1,215,006
Messages
6,122,666
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top