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
Best Answer

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?
Answers

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?
Help Article Resources
Categories
Check out the Formula Handbook template!