#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PAL PAL is offline
external usenet poster
 
Posts: 200
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PAL PAL is offline
external usenet poster
 
Posts: 200
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PAL PAL is offline
external usenet poster
 
Posts: 200
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
cycle time mmb Charts and Charting in Excel 1 August 21st 08 03:24 PM
More Planned and Actual Cycle Times PAL Excel Worksheet Functions 5 January 17th 08 09:35 PM
Time Cycle Antonio Excel Discussion (Misc queries) 7 December 4th 07 12:01 AM
2 cycle logarithmic? Amanda Charts and Charting in Excel 1 January 29th 07 09:38 AM
How do I keep result from 1 iteration cycle to use in next cycle? sgl8akm Excel Discussion (Misc queries) 0 July 27th 06 08:28 PM


All times are GMT +1. The time now is 02:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"