Solved

Workflow Automation - Providing Future Dates

  • 16 September 2021
  • 7 replies
  • 61 views

Userlevel 1

I am trying to setup an automation that will calculate an end date. So for example we have 3 custom fields: 

  1. start date
  2. duration
  3. end date 

I want to know if there is a way to set up an automation so that if start date and duration are entered, the end date will automatically calculate.

Additionally I need to automate if start date and end date are both inout, that the duration will be calculated. 

Are either of these automations possible? 

icon

Best answer by Michelle from Copper 20 September 2021, 16:17

View original

This topic has been closed for comments

7 replies

Userlevel 5
Badge +5

Hi @Mindre, yes this is possible! I’ve attached some examples screenshots below.

Before you try this, make sure your fields for Start Date and Duration have field keys. You can check/create them by going to Settings > Customize > Manage Fields on Records > Edit Custom Fields. These are the field keys I used for this example:

  • Start Date is a Date Field and the field key is start_date
  • Duration is a Number Field and the field key is duration

You’ll notice in the examples these field keys are written as cf_start_date and cf_duration. The cf_ just indicates that these are Custom Fields that you created as opposed to Default Fields.

 

Here is the Trigger:

  • We indicated that the Start Date needs to be after Jan 1 2000; this is an arbitrary date. It doesn’t matter what date you enter here as long as you know your Start Date will always be after that date.

 

Here is the Action:

  • The formula is DATEADD(cf_start_date,ROUND(cf_duration))
    • DATEADD takes a date and adds some number of days to it. In this case, we are adding our Duration to the Start Date.
    • ROUND turns the Duration from a decimal number (e.g. 10.0) into an integer (e.g. 10)
  • Remember to change from Standard to Use a Formula or else this won’t run.

 

I tested this workflow in my demo account and it worked great. Go ahead and give it a try and let me know how it goes!

Userlevel 1

Hi @Michelle from Copper 

 

Thanks for your help! For some reason it is not working for me. My start date field key = shoot_start and the shoot duration field key is shootduration. 

 

I’m not sure what I am doing wrong. See below

 

 

Userlevel 5
Badge +5

Hi @Mindre, your trigger is set to go “When a Project is Created.” Try changing it to “When a specific field is updated.”

 

Let me know if that fixes it!

Userlevel 1

Ha that was it, thank you @Michelle from Copper ! 

Is there any way to kind of do the reverse? So if I have the shoot start date and shoot end date is there a way to automate the duration to populate? This is what I have but something must be wrong because it isn’t working: 

 

Userlevel 5
Badge +5

@Mindre fantastic!

To calculate the duration from the start and end dates, you’ll want to use the DAYS function. It takes two different dates and returns the number of days between them.

Try this:

DAYS(cf_shoot_start,cf_shootwrap)

Userlevel 1

that worked! Thank you so much for all your help!

Userlevel 5
Badge +5

@Mindre Woohoo!!! 🥳 I’m going to mark my first response as “Best Answer” so that it’s easily searchable. Definitely post again if you have another question!