Workaround to override column formula for dates?

Hi there,

I have a sheet with 8,000 rows. In the sheet, each row has a task, task start date and task end date. When someone adds a start date to the first task, the rest of the dates are calculated with a formula, based on a lookup table that has values for how long each task takes.

If I have 10 tasks, I want the formula to calculate all the task start and end dates. I can do this easily with a column formula. But, sometimes the dates need to be adjusted such as if someone finishes a task late. My understanding is that I cannot use a column formula if users need to be able to manually update the dates.

Is my only other option to use cell formulas for each of the 8,000 rows?

Many thanks!

Jackie

Find more posts tagged with

Sheets

Formulas

columns

Accepted answers

KPH

Hi Jackie

There is another option.

You could add another column for the manual adjustments. Then in your column formula you would have an IF ISBLANK or IF ISDATE formula. If the new column is blank or not a date (depending on whether you use ISBLANK or ISDATE) use the formula that you currently have. If the new column contains something or is a date (again depending on which function you use), use that.

Does that make sense?

All comments

KPH

Hi Jackie

There is another option.

Does that make sense?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.