Establishing Automatic Due Dates

2TrakMind

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)

Mike Wilday

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

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

Mike Wilday

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

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

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. 

 

2TrakMind

That's it! Thanks for your help!

Mike Wilday

Absolutely. Glad I could be of assistance! 

Michael Paschke

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