Establishing Automatic Due Dates

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)

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.