excel - How to add a timestamp when a cell data is equal to certain values -

i looking solution on how enter 2 timestamps based on 1 cell's value without either 1 going blank when changed.

example: column has dropdown list "completed" , "pending audit" - want time stamp appear in column 17 same row when pending audit selected. , once changes pending audit completed same row, enter timestamp column 18. way 2 timestamps created pending completion , can documented analyze how time took pending completion.

timestamp format "m:d:y h:m"

format columns q , r meet requirements , enter small event macro:

private sub worksheet_change(byval target range)    dim range: set = range("a:a")    dim v string    if intersect(target, a) nothing exit sub     application.enableevents = false       v = target.value       if v = "pending audit" target.offset(0, 16) = now()       if v = "completed" target.offset(0, 17) = now()    application.enableevents = true end sub 

because worksheet code, easy install , automatic use:

1. right-click tab name near bottom of excel window
2. select view code - brings vbe window
3. paste stuff in , close vbe window

if have concerns, first try on trial worksheet.

if save workbook, macro saved it. if using version of excel later 2003, must save file .xlsm rather .xlsx

to remove macro:

1. bring vbe windows above
2. clear code out
3. close vbe window

to learn more macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

to learn more event macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

macros must enabled work!