If formula to auto-add in a date when action completed

[JeninQC]

Hello,

I am working on a large spreadsheet tracking paperwork in and out - in column O I have a drop down menu with the following options: Created; Sent; Approved; Pending; Chased. I also have a comments field in Column AC

When I select Sent on the drop down menu, I would like the date added in column AC in dd-mm format. I know that I would need an If function, but cannot figure out the bit to add the date. This way when it's time to chase paperwork I can sort my spreadsheet by date order and chase the oldest first.

Can anyone help me with this?

Thanks in advance.

J

[63falcondude]

I don't think that a formula solution is going to do what you are expecting here.

You can put this in AC2:

=IF(O2="Sent",TODAY(),"")

but TODAY recalculates every time that anything is done in the workbook which means that that date will constantly change unless you copy/paste values before the next day.

[Glenn Kennedy]

File/Options/Formulas and enable iterative calculations. Then use this formula which will insert a non-updating date stamp:

=IF(O2<>"Sent","",IF(AC2="",TODAY(),AC2))

[JeninQC]

Hi,
Thanks for this - Just hope that I remember to copy/paste values before I close the spreadsheet at the end of each day!

:-)

Cheers

J

[63falcondude]

You're welcome, happy to help. I would try out Glenn's suggestion as well although personally, I do not like adjusting settings for a single task.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[AliGW]

You could always run a little macro to change formulae to values when you close down the workbook. Post in the VBA section if you need help doing this.