Add a time increment when a condition is met.

Hello again,

I have two columns C & D. In col C (C10) a time is entered (either 13.45 or 0:33.22 format). If column D (D10) is blank then the time in Col C stays the same (13.45 or 0:33.22). However if Col D has a M in the cell (D10) I want to add 0.24 to the time in Col C (C10) eg 13.69 or 0:33.46. I have tried various formula combinations but keep getting circular reference errors. Any suggestions.

Thanks

RunHard

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

You will need to use VBA if you wish to alter the initial value (iteration (to permit circular references) won't help long term) - is that viable ? If not use another cell to store the final (possibly adjusted) time.

My point re: long term related to Iteration and Circular References.

You state that this adjustment will need to be applied on an ad hoc basis... I honestly think your best bet would be to use a "final time" column, have a flag cell somewhere that indicates as to whether or not times need to be adjusted and incorporate that into your final time calculations.

For arguments sake let's say A1 holds the flag for adjustments - Y/N, and let's say E10 will hold adjusted time based on C10 (time), D10 (M) and of course A1 (Y)

E10: =$C10+("0:0:24"*AND($A$1="Y",$D10="M"))
copied down

(note I assume you meant 24 seconds ?)

You can of course protect the sheet such that the cells in E are locked and thus do not permit data entry (to avoid confusion)

VBA is obviously reliant upon users enabling macros in the first instance - if you feel your end users are not overly XL savvy then this is inherently risky and would require code such that the file is to all intents and purposes disabled until such time as macros are enabled so as to ensure the file is always in use as per design.