Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dates
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 |
#2
|
|||
|
|||
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:$IV 3)))-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) "pjd" wrote in message ... 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 |
#3
|
|||
|
|||
On Tue, 7 Dec 2004 18:09:03 -0800, pjd wrote:
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 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 |
#4
|
|||
|
|||
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) "Peo Sjoblom" wrote in message ... 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:$IV 3)))-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) "pjd" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
grouping dates by week/month/etc. on cat. axis | Charts and Charting in Excel | |||
Ploting dates against a calendar and not as a simple events | Charts and Charting in Excel | |||
How do I import fractions without data being converted to dates? | Excel Discussion (Misc queries) | |||
Dates in spreadsheets | Excel Discussion (Misc queries) | |||
How do I display months and years between two dates | Excel Discussion (Misc queries) |