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
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!