| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Start and End date
I have two columns column A is formatted with the date for the year column b
has data. I would like at the top of the page to display the date that data starts to show and the date that data stops for the year. Thanks for the assist Example: A B 01/01/08 01/02/08 1 01/03/08 1 01/04/08 01/05/08 1 01/06/08 01/07/08 1 01/08/08 1 01/09/08 01/10/08 Result Start 01/02/08 End 01/08/08 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Start and End date
=INDEX(A:A,MIN(IF(B1:B20=1,ROW(A1:A20))))
and =INDEX(A:A,MAX(IF(B1:B20=1,ROW(A1:A20)))) which are array formulae, they should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lisa" wrote in message ... I have two columns column A is formatted with the date for the year column b has data. I would like at the top of the page to display the date that data starts to show and the date that data stops for the year. Thanks for the assist Example: A B 01/01/08 01/02/08 1 01/03/08 1 01/04/08 01/05/08 1 01/06/08 01/07/08 1 01/08/08 1 01/09/08 01/10/08 Result Start 01/02/08 End 01/08/08 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Start and End date
Try these:
For start, an *array* formula: =INDEX(A1:A10,MATCH(TRUE,(B1:B10<""),0)) For end, a regular entry formula: =INDEX(A1:A10,MATCH(99^99,B1:B10)) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Lisa" wrote in message ... I have two columns column A is formatted with the date for the year column b has data. I would like at the top of the page to display the date that data starts to show and the date that data stops for the year. Thanks for the assist Example: A B 01/01/08 01/02/08 1 01/03/08 1 01/04/08 01/05/08 1 01/06/08 01/07/08 1 01/08/08 1 01/09/08 01/10/08 Result Start 01/02/08 End 01/08/08 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determining an annual review date from an employee start date | Excel Worksheet Functions | |||
| How to programatically determine the start date and end date of a graph | Charts and Charting in Excel | |||
| Minimum Date Greater Than Observation Start Date | Excel Discussion (Misc queries) | |||
| Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
| how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel | |||
Linear Mode
