Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating earliest start and latest end dates by contract
Hi everyone,
I went through some of the questions and have not been successful at finding the simplest and concise way of tracking contracts for my reporting purposes. Currently, I track multiple tasks for one contract number. As a result, I end up have multiple task dates for one project that I will need to summarize into a monthly report. At this point, I have been doing it manually and has been quite time consuming. I am hoping there is a faster way for me to get the same results. Here is an example of what I'm after. I have a report that looks like the example below: Contract Task Start Date Task End Date 101010 01/05/99 12/3/05 101010 12/8/04 11/5/07 356 08/06/05 4/8/07 12345 11/3/89 11/01/01 12345 10/6/01 12/4/07 Now with that said, what are the steps I should take and the formula I should use to get a result similar to this kind: Contract Task Start Date Task End Date 101010 01/05/99 11/5/07 356 08/06/05 4/8/07 12345 11/3/89 12/4/07 Any help is GREATLY appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating earliest start and latest end dates by contract
I may have lost my reply
select contract_list use <data<filter<advanced filter { unique list} to get a unique contract list if you put it in Column D with the lable in D1 in E1 enter "TaskStart Date" in E2 enter =Min(if(Contract_range=D2,Task_Start_Date_range,ma x(Task_Start_Date_range))) enter as an array control-shirft-enter copy down to the end of the unique list in F2 enter =Mmax(if(Contract_range=D2,Task_end_Date_range,min (Task_end_Date_range))) again enter as an array function and copy down "Data Analyst" wrote: Hi everyone, I went through some of the questions and have not been successful at finding the simplest and concise way of tracking contracts for my reporting purposes. Currently, I track multiple tasks for one contract number. As a result, I end up have multiple task dates for one project that I will need to summarize into a monthly report. At this point, I have been doing it manually and has been quite time consuming. I am hoping there is a faster way for me to get the same results. Here is an example of what I'm after. I have a report that looks like the example below: Contract Task Start Date Task End Date 101010 01/05/99 12/3/05 101010 12/8/04 11/5/07 356 08/06/05 4/8/07 12345 11/3/89 11/01/01 12345 10/6/01 12/4/07 Now with that said, what are the steps I should take and the formula I should use to get a result similar to this kind: Contract Task Start Date Task End Date 101010 01/05/99 11/5/07 356 08/06/05 4/8/07 12345 11/3/89 12/4/07 Any help is GREATLY appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating earliest start and latest end dates by contract
Typo:
Both formulas should reference cell A10, not A11. Biff "T. Valko" wrote in message ... With this data in the range A2:C6 101010 01/05/99 12/3/05 101010 12/8/04 11/5/07 356 08/06/05 4/8/07 12345 11/3/89 11/01/01 12345 10/6/01 12/4/07 A10 = 101010 Try these array formulas** For the min start date: =MIN(IF(A2:A6=A11,B2:B6)) For the max end date: =MAX(IF(A2:A6=A11,C2:C6)) Format as DATE ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Data Analyst" <Data wrote in message ... Hi everyone, I went through some of the questions and have not been successful at finding the simplest and concise way of tracking contracts for my reporting purposes. Currently, I track multiple tasks for one contract number. As a result, I end up have multiple task dates for one project that I will need to summarize into a monthly report. At this point, I have been doing it manually and has been quite time consuming. I am hoping there is a faster way for me to get the same results. Here is an example of what I'm after. I have a report that looks like the example below: Contract Task Start Date Task End Date 101010 01/05/99 12/3/05 101010 12/8/04 11/5/07 356 08/06/05 4/8/07 12345 11/3/89 11/01/01 12345 10/6/01 12/4/07 Now with that said, what are the steps I should take and the formula I should use to get a result similar to this kind: Contract Task Start Date Task End Date 101010 01/05/99 11/5/07 356 08/06/05 4/8/07 12345 11/3/89 12/4/07 Any help is GREATLY appreciated! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating earliest start and latest end dates by contract
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Earliest and latest dates that can be correctly represented in Exc | Excel Discussion (Misc queries) | |||
find latest date in a row, when dates have apostrophes in it | Excel Discussion (Misc queries) | |||
How do I find the earliest dates in a range of dates? | Excel Worksheet Functions | |||
Find the earliest date in a range of dates? | Excel Worksheet Functions | |||
Return the latest date from a list of dates | Excel Worksheet Functions |