Establishing Automatic Due Dates

Options
2TrakMind
2TrakMind
edited 12/09/19 in Formulas and Functions

I have a project that has a launch date of July 2, which is represented in a column. I would like to have a due date be automatically calculated 63 days prior to this date, in another column. I have created a formula that appears to work, in principle, but rather than setting the date 66 days prior to the July 2 (April 27), it is setting it 96 days prior (March 27). I've never done this before, so any help would be greatly appreciated! Here is my formula. 

=WORKDAY([Devotion Date]1, -63)

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    That is because you are using the workday function which is subtracting 63 work days. Try this: =[Devotion Date]1 - 63. That will subtract 63 calendar days from the devotion 1 date. 

  • 2TrakMind
    Options

    Thanks for the input. I just deduced that, myself. I tried your formula, but it displays #UNPARSEABLE. Any other suggestions? Thanks!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Make sure the column you are putting it into is set up as a date column. 

    Then check the spelling of the column name. Make sure you are using normal quotes... copy and paste it into notepad or something like that. To make sure no automatic formatting was applied by html or a word-processor.

    Also, can you paste the formula in here that you are using so we can review it?

  • 2TrakMind
    2TrakMind
    edited 03/15/18
    Options

    The columns are both formatted as Date. My formula is working, it is just not counting weekends. This is my formula...

    =WORKDAY([Devotion Date]1, -63)

    I understand that using WORKDAY is the reason it is omitting the weekends, and I can make it work, but I would rather the calculation be based on actual days. Is there another word that will do this?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    I have attached an example from a test sheet I created. Does your column name have any spaces or numbers at the end of the name? If not, try removing the brackets from the formula I gave you earlier. Please see my screenshots showing that the formula for removing calendar days works. 

     

    2018-03-15_08-37-06.jpg

    2018-03-15_08-43-52.jpg

  • 2TrakMind
    Options

    That's it! Thanks for your help!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Absolutely. Glad I could be of assistance! 

  • Michael Paschke
    Options

    I have the due date going off of the created date that is automatically created. It says #UNPARSEABLE until the date is created.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!