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. |
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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 06:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com