I am trying to create a report that shows how long an opportunity stays in each opportunity stage before it's closed. Here are the methods i tried to capture the dates and the number of days:
1) I created date fields for each stage to capture the date a stage was moved and integer fields to calculate the difference between the previous date and the new date to get the number of days. The problem with this method is that the number of days are calculated sequentially. e.g from stage 1.0 to 2.0 but not stage 1.0 to 4.0. i will like it to capture/update the dates and calculate the difference regardless of which stage the opportunity was moved to. The calculated fields codes are below:
ifElse(equal($lead_stage_c,"1.0 Suspect Marketing"),today(),$date_prospect_calling_c)
2) I created a workflow to capture the date an opportunity changes to a new stage and the date it changes from that stage and an integer field that calculates the difference. That means two things are captured: the date it changed from that stage and the date for the new stage. BUT when i change to a new stage, Sugar is able to give me the date it changed from but not the new date for the new stage. The dates are also not updated even though my workflow is set to "All Updates".
Screenshot is attached.
If anyone knows a better way to achieve this task, pleaseeeeee help! Thanks