asked on

Update cell in date column to a new date based on data in other cells

Column G is a date column that show the last time a chapter was updated.

I would like the date in Column G to be automatically updated to a static date (example of format: "15 NOV 14") if the following criteria is met:
If Column I, Column N, Column S, Column X, Column AC OR Column AH = "NEW" or "REVISED"
Otherwise, the pre-existing date in Column G is to remain intact and unchanged.

I've attached a sample spreadsheet.

Thanks,
Andrea
LastRevisionDateUpdate-sample.xlsm

Glenn Ray

The dates in your example workbook are static dates (i.e, constants) right now. Would these be formulas usually? If not, what should the new date be converted to? Today's date? Another calculated date based on other data?

A Worksheet_Change event should handle this, but will wait to hear clarification from you.

Regards,
-Glenn

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

Terrific, Glenn...the only modification I made was to line 16 (as shown below) since we use the same date for all changes within a cycle (and not the actual date the change is done on). So for each cycle, I'll update the code with the new 'static' date to be used.

cl.Value = Format("15-Nov-14", "dd-mmm-yy")

Thanks so much!

Cheers,
Andrea

Glenn Ray

You're welcome.
-Glenn