| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating whether solution is in time or not
Dear Excel(lent) users,
I have a list in which al sent requests are logged. Every request has a priority, each priority defines in what time a request needs to be executed. To clarify: In Column A I use the date of the sent request In Column B I use the date of the request being executed In Column C I use the priority of the request Column A Column B Column C -------------- ------------- ---------- 01-01-2008 02-01-2008 1 01-02-2008 02-02-2008 2 01-03-2008 02-03-2008 3 01-04-2008 08-04-2008 1 01-04-2008 08-05-2008 4 01-04-2008 08-06-2008 5 If you take the following maximum processing time into consideration: Priority max days to process -------- ----------------------- 1 1 2 2 3 5 4 10 5 15 I am looking for a formula which would give me the following information: Priority Deadline met Deadline not met -------- --------------- -------------------- 1 1 1 2 1 0 3 1 0 4 0 1 5 0 1 Can you please help me ?? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating whether solution is in time or not
On Nov 6, 10:45*pm, The Fool on the Hill
wrote: *Dear Excel(lent) users, I have a list in which al sent requests are logged. Every request has a priority, each priority defines in what time *a request needs to be executed. To clarify: In Column A I use the date of the sent request In Column B I use the date of the request being executed In Column C I use the priority of the request Column A * * * Column B * * Column C -------------- * ------------- * ---------- 01-01-2008 * *02-01-2008 * 1 01-02-2008 * *02-02-2008 * 2 01-03-2008 * *02-03-2008 * 3 01-04-2008 * *08-04-2008 * 1 01-04-2008 * *08-05-2008 * 4 01-04-2008 * *08-06-2008 * 5 If you take the following maximum processing time into consideration: Priority * *max days to process -------- * *----------------------- 1 * * * * * *1 2 * * * * * *2 3 * * * * * *5 4 * * * * * *10 5 * * * * * *15 I am looking for a formula which would give me the following information: Priority * * * *Deadline met * * * *Deadline not met -------- * * * *--------------- * * * *-------------------- 1 * * * * * * * * * * * * 1 * * * * * * * * * * * * * * 1 2 * * * * * * * * * * * * 1 * * * * * * * * * * * * * * 0 3 * * * * * * * * * * * * 1 * * * * * * * * * * * * * * 0 4 * * * * * * * * * * * * 0 * * * * * * * * * * * * * * 1 5 * * * * * * * * * * * * 0 * * * * * * * * * * * * * * 1 Can you please help me ?? Maybe... =SUMPRODUCT(--($B$2:$B$7-$A$2:$A$7<=VLOOKUP(E2,$I$2:$J$6,2,FALSE)),-- ($C$2:$C$7=E2)) in F2, filled down to F6, for Deadline met. Priorities 1-5 in E2:E6 and... =COUNTIF($C$2:$C$7,E2)-F2 in G2 filled down to G6 for deadline not met. Lookup table for Priority/max days to process in $I$2:$J$6. Ken Johnson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating whether solution is in time or not
I put the original data with dates in cells A1:C6. I put you Priority in
cells A10:B14. then I added to the priority table columns C and D (C10:C14) the deaddline Met and Deadline not Met. These are the formulas fro cells C10 and D10 =SUMPRODUCT(--($C$1:$C$6=$A10),--($B$1:$B$6-$A$1:$A$6<=$B10)) =SUMPRODUCT(--($C$1:$C$6=$A10),--($B$1:$B$6-$A$1:$A$6$B10)) "The Fool on the Hill" wrote: Dear Excel(lent) users, I have a list in which al sent requests are logged. Every request has a priority, each priority defines in what time a request needs to be executed. To clarify: In Column A I use the date of the sent request In Column B I use the date of the request being executed In Column C I use the priority of the request Column A Column B Column C -------------- ------------- ---------- 01-01-2008 02-01-2008 1 01-02-2008 02-02-2008 2 01-03-2008 02-03-2008 3 01-04-2008 08-04-2008 1 01-04-2008 08-05-2008 4 01-04-2008 08-06-2008 5 If you take the following maximum processing time into consideration: Priority max days to process -------- ----------------------- 1 1 2 2 3 5 4 10 5 15 I am looking for a formula which would give me the following information: Priority Deadline met Deadline not met -------- --------------- -------------------- 1 1 1 2 1 0 3 1 0 4 0 1 5 0 1 Can you please help me ?? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating whether solution is in time or not
Thank you very much !
"Ken Johnson" wrote: On Nov 6, 10:45 pm, The Fool on the Hill wrote: Dear Excel(lent) users, I have a list in which al sent requests are logged. Every request has a priority, each priority defines in what time a request needs to be executed. To clarify: In Column A I use the date of the sent request In Column B I use the date of the request being executed In Column C I use the priority of the request Column A Column B Column C -------------- ------------- ---------- 01-01-2008 02-01-2008 1 01-02-2008 02-02-2008 2 01-03-2008 02-03-2008 3 01-04-2008 08-04-2008 1 01-04-2008 08-05-2008 4 01-04-2008 08-06-2008 5 If you take the following maximum processing time into consideration: Priority max days to process -------- ----------------------- 1 1 2 2 3 5 4 10 5 15 I am looking for a formula which would give me the following information: Priority Deadline met Deadline not met -------- --------------- -------------------- 1 1 1 2 1 0 3 1 0 4 0 1 5 0 1 Can you please help me ?? Maybe... =SUMPRODUCT(--($B$2:$B$7-$A$2:$A$7<=VLOOKUP(E2,$I$2:$J$6,2,FALSE)),-- ($C$2:$C$7=E2)) in F2, filled down to F6, for Deadline met. Priorities 1-5 in E2:E6 and... =COUNTIF($C$2:$C$7,E2)-F2 in G2 filled down to G6 for deadline not met. Lookup table for Priority/max days to process in $I$2:$J$6. Ken Johnson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating whether solution is in time or not
Thank you very much !
"Joel" wrote: I put the original data with dates in cells A1:C6. I put you Priority in cells A10:B14. then I added to the priority table columns C and D (C10:C14) the deaddline Met and Deadline not Met. These are the formulas fro cells C10 and D10 =SUMPRODUCT(--($C$1:$C$6=$A10),--($B$1:$B$6-$A$1:$A$6<=$B10)) =SUMPRODUCT(--($C$1:$C$6=$A10),--($B$1:$B$6-$A$1:$A$6$B10)) "The Fool on the Hill" wrote: Dear Excel(lent) users, I have a list in which al sent requests are logged. Every request has a priority, each priority defines in what time a request needs to be executed. To clarify: In Column A I use the date of the sent request In Column B I use the date of the request being executed In Column C I use the priority of the request Column A Column B Column C -------------- ------------- ---------- 01-01-2008 02-01-2008 1 01-02-2008 02-02-2008 2 01-03-2008 02-03-2008 3 01-04-2008 08-04-2008 1 01-04-2008 08-05-2008 4 01-04-2008 08-06-2008 5 If you take the following maximum processing time into consideration: Priority max days to process -------- ----------------------- 1 1 2 2 3 5 4 10 5 15 I am looking for a formula which would give me the following information: Priority Deadline met Deadline not met -------- --------------- -------------------- 1 1 1 2 1 0 3 1 0 4 0 1 5 0 1 Can you please help me ?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Time with Date/Time checking in formula | Excel Worksheet Functions | |||
| Calculating Regular time, overtime and double time | Excel Worksheet Functions | |||
| calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
| Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
Linear Mode
