Dates

P

pjd

Hi

I have a spreadsheet. Names listed down in column A and dates across the top.

In each row I have put a "L" at the beginning date of the employees' holiday
and drag it across to the last day of holiday.

What I would like to do in the end cloumn is (a formula??) to search across
the row from left to right, find the first "L" lookup up the dates at the top
of the column. And in the next cell do the same but look for the last "L: so
that I end up with;

Name First Day of Leave Last Day of Leave.
Jones A 15/12/04 06/01/05

I have many many names, so, if this could be done it would save me hours and
hours.

Thanks

All help much appreciated.

Thanks
 
P

Peo Sjoblom

One way, I assume here that the Ls we are checking is for person X in row 3,
the dates in row 1 and that the values start in column B going across

To get the start date use

=INDEX($B1:$IV1,MATCH("L",$B3:$IV3,0))

copy down

the end date

=INDEX($B1:$IV1,MAX(($B3:$IV3="L")*(COLUMN($B3:$IV3)))-1)

entered with ctrl + shift & enter

note the minus 1, if the dates started in A1 ther wouldn't be anything, if
they start in column D use -3

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
R

Ron Rosenfeld

Hi

I have a spreadsheet. Names listed down in column A and dates across the top.

In each row I have put a "L" at the beginning date of the employees' holiday
and drag it across to the last day of holiday.

What I would like to do in the end cloumn is (a formula??) to search across
the row from left to right, find the first "L" lookup up the dates at the top
of the column. And in the next cell do the same but look for the last "L: so
that I end up with;

Name First Day of Leave Last Day of Leave.
Jones A 15/12/04 06/01/05

I have many many names, so, if this could be done it would save me hours and
hours.

Thanks

All help much appreciated.

Thanks
Click to expand...

I'm not quite sure how many columns you are using but I figured 31 for this
exercise. You may need to change some references depending on the layout of
your worksheet.

First Day of Leave (in AG2):

=INDEX($B$1:$AE$1,1,MATCH("L",B2:AF2,FALSE))

Last Day of Leave:

=AG2+COUNTIF(B2:AF2,"L")-1



--ron
 
P

Peo Sjoblom

Change the index part to $B$1:$IV$1

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Top