Forum Discussion
I need help with a formula
I am working on a Monthly Cycle Time Report for vehicles I have in service. I need a formula to show how many are completed once I input the RO close time.
EXAMPLE:
| STOCK | RO | DATE OPEN | TIME OPEN | DATE CLOSE | TIME CLOSE | TOTAL | TYPE | TYPE | TOTAL | TOTAL DONE | ||
| A1 | 1268 | 08/24/20 | 10:14 | 8/27/2020 | 12:00 | 3.07 | CPO | CPO | 6 | |||
| A2 | 1271 | 08/24/20 | 10:32 | 8/28/2020 | 13:00 | 4.10 | CPO | ASIS | 4 | |||
| A3 | 1284 | 08/24/20 | 10:53 | 8/29/2020 | 14:00 | 5.13 | CPO | EXLC | 1 | |||
| A4 | 1288 | 08/24/20 | 11:04 | 8/30/2020 | 15:00 | 6.16 | ASIS | STDT | 2 | |||
| A5 | 1437 | 08/25/20 | 10:02 | 8/31/2020 | 16:00 | 6.25 | EXLC | LEASE RETURN | 1 | |||
| A6 | 1448 | 08/25/20 | 10:18 | 9/1/2020 | 17:00 | 7.28 | ASIS | TOTAL | 14 | |||
| A7 | 1458 | 08/25/20 | 10:34 | 9/2/2020 | 18:00 | 8.31 | STDT | |||||
| A8 | 1462 | 08/25/20 | 10:39 | 9/3/2020 | 19:00 | 9.35 | LEASE RETURN | |||||
| A9 | 1463 | 08/26/20 | 11:39 | CPO | ||||||||
| A10 | 1464 | 08/27/20 | 12:39 | ASIS | ||||||||
| A11 | 1465 | 08/28/20 | 13:39 | STDT | ||||||||
| A12 | 1466 | 08/29/20 | 14:39 | ASIS | ||||||||
| A13 | 1467 | 08/30/20 | 15:39 | CPO | ||||||||
| A14 | 1468 | 08/31/20 | 16:39 | CPO |
Few variants. You may create PivotTable. You may use dynamic arrays formulas if your version of Excel supports it. You may use COUNIFS on per-cell basis if you have pre-defined list of types.
Formulas are in attached file.
3 Replies
Few variants. You may create PivotTable. You may use dynamic arrays formulas if your version of Excel supports it. You may use COUNIFS on per-cell basis if you have pre-defined list of types.
Formulas are in attached file.
I think I will create a pivot table to help stay organized . I will try these formulas out. Thank you SergeiBaklan
CrimsonTrail , you are welcome
