ShowAll Questionssorted byDate Posted
Gaspar

# Formula Help - Count number of days and populate a date field

Use Case: For an internal system that crowd sources innovative ideas (Innovations) and recommendations from across the globe in order to become a more effective and connected organization.

Specs: The submission progression goes from Idea (Stage1) to Concept (Stage 2) to Prototype (Stage 3) to Execution (Stage 4). Each stage has different fields, due dates and individual forms. The formula lies in Due Dates. Currently, we have it set up so that Stage 2 is 20 days after Stage 1 Submission Date, and so on. One requested data point for Stage 2, 3 and 4 is "Number of Days estimated until Completion of Innovation." This values needs to be used to allow the submitting team to set the due date for the next form/next stage. I want this individualized approach to each innovation instead of having it based on a cross cutting 20 days-from-submission-date formula.

Formula should: Add the number value in the "Number of Days estimated until Completion of Innovation" to Stage 1/2/3 Submission Date and populate Stage 2/3/4 Due Dates given that calculation.

Example: If my submission date for Stage 2 is May 1, 2013, and my "Number of Days" field is 15 - upon submission the formula should calculate that the Stage 3 Due Date is May 16, 2013.

Any thoughts/solutions are greatly appreciated!
Venkat Polisetti

I believe, you may have to create multiple formula fields to get to the due dates depending on my understanding of your requirements:

1. Create a formula field - "Stage 2 Due Date" with the formula: Stage 2 Submission Date + Number of Days for Stage 2"
2. Create another formula filed - "Stage 3 Due Date" with the formula: Stage 2 Due Date + Number of Days for Stage 3"
3. and so on...

Hope this helps.

DanielBProbert

if i think i've understood you correctly that you want the person that creates the records to be able to manage the time frames then you'll need custom fields for them to enter the days in.

i.e.

During the Initial record creation they could have a custom field for Stage 1 Period - this could be numerical. Then you have a formula for Stage 1 Date that adds CreatedDate + Stage 1 Period

For Stage 2 you would have a formula that is Stage 1 Date + Stage 2 Period

Do you then have approval process that move you through the stages - i.e. when you hit the stage 1 end date does the record type automatically change to Stage 2, if so you could replicate the above except have an email alert telling the creator of the record that they need to update the Stage 2 Period.

I think this should work.

cheers

dan