Calculate a Project Start Date Based on numWorkdays Before Project Delivery Date

[Eric_Owsley]

I have a base that tracks creative campaigns with hard drop dates. I want to be able to calculate a start date for an individual campaign based on the projected number of workdays required to walk the campaign through a production cycle. My production cycles are housed in a related table with a column for each stage and a number in days for the amount of time necessary to complete each stage. Each row has a summed value for the total number of days for the production cycle. In my campaigns table, I want to calculate a start date based on the publish date by subtracting the number of days to complete the production cycle. I’m trying to use the WORKDAY function like so …

WORKDAY({Drop Date}, -{Lead Time})

When I give it a drop date of 6/5/2019 and subtract 14 days (the rollup value from my cycles table), it returns a date of 5/27/2019, which represents a difference of 9 workdays in the 14-day stretch I supplied the function. I need to figure out how to subtract 14 workdays, making the calculated date 5/21/2019.

Any advice would be greatly appreciated. Thanks.

[Eric_Owsley · Accepted solution]

Cancel the above. The Workday function does exactly what I wanted. I just messed up the value I was supplying for numDays. My bad. Thanks.

[W_Vann_Hall]

Using that formula and values, I get a date of 5/16/2019 (or 5/15/2019, if I have mismatched GMT toggles), which is 14 workdays prior to 6/5/2019. (5/21/2019 would be a shift of 11 workdays.)

I’m using Chrome on WIndows 10, so there could possibly be a platform-related issue. Double-check your base, and if you’re still getting an anomalous response, contact Support. (You can email at support@airtable.com or send a message directly from the Airtable app; click ‘Help’ and choose ‘Contact us.’ This page has info on reporting errors to Airtable.)

If you can hold on a few days (knock wood; I think I’m currently on the waiting list of half the oral surgeons in town, so my ability to concentrate has been a bit erratic these past few weeks), I’m about to publish a framework for scheduling apps that will let you define tasks as a collection of subtasks and deliverables, create a new task, and have all the dependent dates populate automatically. Derived dates can be calculated in days or workdays before or after a primary (manually entered) date, and they can be overridden, if necessary. Primary dates can be scheduled via drag-and-drop in a calendar view — even for date ranges with a calculated end date — and the schedule can contain a mix of task-defined and ad hoc dates and date ranges. Finally, a second calendar view strips out all irrelevant dates (such as a primary date flagged to be shifted to the nearest workday before or after the actual date — a mid-month meeting or employee birthday, for instance) to show only essential due dates, a view that should be iCalendar-friendly. Right now, it’s about 80% complete; I’ve been nibbling at it when my teeth allow (you see what I did there?), and, with luck, it should be finished and poorly documented within a few days…