Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cycle Times
I am trying to calucate cycle times. I have 2 milestones (each with a
planned and actual date Column A: Milestone 1 Planned Column B: Milestone 1 Actual Column C: Milestone 2 Planned Column D: Milestone 2 Actual If the period between the two periods has occured (Actual), then we want to subtract D from B to determine the cycle time. If they are blank then the cycle time is planned and the cycle time must be calculated by Column C- Column B (if it occured) or Column C - Column A. It would be great to distinguish the cycle and whether or not it is planned or actual in one field. ALternatively, we can have a separate column for planned (which would be blank if there was an actual) or actual column. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cycle Times
Perhaps something like this:
=IF(ISBLANK(D2),IF(ISBLANK(B2),A2-C2&" (Plan-Plan)",B2-C2&" (Actual-Plan)"),A2-D2&" (Actual-Actual)") Modify the text as desired -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "PAL" wrote: I am trying to calucate cycle times. I have 2 milestones (each with a planned and actual date Column A: Milestone 1 Planned Column B: Milestone 1 Actual Column C: Milestone 2 Planned Column D: Milestone 2 Actual If the period between the two periods has occured (Actual), then we want to subtract D from B to determine the cycle time. If they are blank then the cycle time is planned and the cycle time must be calculated by Column C- Column B (if it occured) or Column C - Column A. It would be great to distinguish the cycle and whether or not it is planned or actual in one field. ALternatively, we can have a separate column for planned (which would be blank if there was an actual) or actual column. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cycle Times
Hello Luke,
Brilliant. I transposed the calclulation as it was giving negative numbers. Here is what I changed it to: =IF(ISBLANK(D2),IF(ISBLANK(B2),C2-A2&" (Plan-Plan)",C2-B2&" (Actual-Plan)"),D2-B2&" (Actual-Actual)") Final question would be....instead of days, I would like to convert it to months. Generally, based on a benchmarking organization I use, to get months I would go C2-A2/30.43. When I do this it goes out several decimal places. Due to the concatenation I can't round it to tengths. Anyway to get it to months, or should I just take off the concatenation? "Luke M" wrote: Perhaps something like this: =IF(ISBLANK(D2),IF(ISBLANK(B2),A2-C2&" (Plan-Plan)",B2-C2&" (Actual-Plan)"),A2-D2&" (Actual-Actual)") Modify the text as desired -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "PAL" wrote: I am trying to calucate cycle times. I have 2 milestones (each with a planned and actual date Column A: Milestone 1 Planned Column B: Milestone 1 Actual Column C: Milestone 2 Planned Column D: Milestone 2 Actual If the period between the two periods has occured (Actual), then we want to subtract D from B to determine the cycle time. If they are blank then the cycle time is planned and the cycle time must be calculated by Column C- Column B (if it occured) or Column C - Column A. It would be great to distinguish the cycle and whether or not it is planned or actual in one field. ALternatively, we can have a separate column for planned (which would be blank if there was an actual) or actual column. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cycle Times
We can force a decimal check inside the formula with the TEXT function.
=IF(ISBLANK(D2),IF(ISBLANK(B2),TEXT((C2-A2)/30.43,"#.0")&" (Plan-Plan)",TEXT((C2-B2)/30.43,"#.0")&" (Actual-Plan)"),TEXT((D2-B2)/30.43,"#.0")&" (Actual-Actual)") The text function takes a text value, and the 2nd arguement controls the format. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "PAL" wrote: Hello Luke, Brilliant. I transposed the calclulation as it was giving negative numbers. Here is what I changed it to: =IF(ISBLANK(D2),IF(ISBLANK(B2),C2-A2&" (Plan-Plan)",C2-B2&" (Actual-Plan)"),D2-B2&" (Actual-Actual)") Final question would be....instead of days, I would like to convert it to months. Generally, based on a benchmarking organization I use, to get months I would go C2-A2/30.43. When I do this it goes out several decimal places. Due to the concatenation I can't round it to tengths. Anyway to get it to months, or should I just take off the concatenation? "Luke M" wrote: Perhaps something like this: =IF(ISBLANK(D2),IF(ISBLANK(B2),A2-C2&" (Plan-Plan)",B2-C2&" (Actual-Plan)"),A2-D2&" (Actual-Actual)") Modify the text as desired -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "PAL" wrote: I am trying to calucate cycle times. I have 2 milestones (each with a planned and actual date Column A: Milestone 1 Planned Column B: Milestone 1 Actual Column C: Milestone 2 Planned Column D: Milestone 2 Actual If the period between the two periods has occured (Actual), then we want to subtract D from B to determine the cycle time. If they are blank then the cycle time is planned and the cycle time must be calculated by Column C- Column B (if it occured) or Column C - Column A. It would be great to distinguish the cycle and whether or not it is planned or actual in one field. ALternatively, we can have a separate column for planned (which would be blank if there was an actual) or actual column. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cycle Times
Formula works well when I enter the dates manually. If the dates are
exported from a db, it only works in the case of "Actual-Actual". The other scenarios produce "#Value!". My assumption is something about the export messes with the format of the dates. I have checked the format via teh Format dropdown at the top, but there is no impact. Thoughts. "Luke M" wrote: We can force a decimal check inside the formula with the TEXT function. =IF(ISBLANK(D2),IF(ISBLANK(B2),TEXT((C2-A2)/30.43,"#.0")&" (Plan-Plan)",TEXT((C2-B2)/30.43,"#.0")&" (Actual-Plan)"),TEXT((D2-B2)/30.43,"#.0")&" (Actual-Actual)") The text function takes a text value, and the 2nd arguement controls the format. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "PAL" wrote: Hello Luke, Brilliant. I transposed the calclulation as it was giving negative numbers. Here is what I changed it to: =IF(ISBLANK(D2),IF(ISBLANK(B2),C2-A2&" (Plan-Plan)",C2-B2&" (Actual-Plan)"),D2-B2&" (Actual-Actual)") Final question would be....instead of days, I would like to convert it to months. Generally, based on a benchmarking organization I use, to get months I would go C2-A2/30.43. When I do this it goes out several decimal places. Due to the concatenation I can't round it to tengths. Anyway to get it to months, or should I just take off the concatenation? "Luke M" wrote: Perhaps something like this: =IF(ISBLANK(D2),IF(ISBLANK(B2),A2-C2&" (Plan-Plan)",B2-C2&" (Actual-Plan)"),A2-D2&" (Actual-Actual)") Modify the text as desired -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "PAL" wrote: I am trying to calucate cycle times. I have 2 milestones (each with a planned and actual date Column A: Milestone 1 Planned Column B: Milestone 1 Actual Column C: Milestone 2 Planned Column D: Milestone 2 Actual If the period between the two periods has occured (Actual), then we want to subtract D from B to determine the cycle time. If they are blank then the cycle time is planned and the cycle time must be calculated by Column C- Column B (if it occured) or Column C - Column A. It would be great to distinguish the cycle and whether or not it is planned or actual in one field. ALternatively, we can have a separate column for planned (which would be blank if there was an actual) or actual column. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cycle time | Charts and Charting in Excel | |||
More Planned and Actual Cycle Times | Excel Worksheet Functions | |||
Time Cycle | Excel Discussion (Misc queries) | |||
2 cycle logarithmic? | Charts and Charting in Excel | |||
How do I keep result from 1 iteration cycle to use in next cycle? | Excel Discussion (Misc queries) |