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.
|