When data in a cell is deleted, the formlua is also deleted - how to get round this?

Hi,

I'm working on a training records report and have a 'Date Completed' column and a 'Renewal Due Date' column. The renewal due date column has a formula in it to calculate the completed date + 365 days =SUM(Y6+365). When I copy the formula down the column it enters a seemingly random '30-Dec-00' date into all the cells where there is no corresponding completed date (i.e. where the employee has never completed the training). When I delete this random date it also deletes the formula.

I want the formula to only enter a date when there is a date in the cooresponding 'Date Completed' cell. Alternatively, I would like to be able to delete this random date but keep the formula intact so that when the employee does complete the training and a date is entered in the corresponding 'Date Completed' cell, the renewal date would also appear.

Can anyone help? Thanks very much.

Originally Posted by Ron Coderre

Try something like this:

Formula:

Please Login or Register  to view this content.

Is that something you can work with?

Ah, thank you Ron Coderre. Makes perfect sense and works a treat! Much appreciated.