The value in F is not "null", it just looks blank because of the "".

The actual value in F is FALSE because you have change from "ongoing" to
"complete"

Using your DateAndTime Function produces this.

You may be better off with event code that produces a static date and time in
the cells of F and G

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Then
n = Target.Row
If Excel.Range("E" & n).Value = "on going" Then
Target.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
ElseIf Excel.Range("E" & n).Value = "complete" Then
Target.Offset(0, 2).Value = Format(Now, "dd mmm yyyy h:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

On 4 Dec 2006 08:27:37 -0800, wrote:

Hello,

I am trying to capture a date stamp in a cell when a certain option has
been selected from a drop down menu. After searching on this forum i
made use of

Function DateAndTime()
DateAndTime = Now
End Function

Essentially I have 2 options in my drop down list. "On Going" and
"Complete"

I have 2 further columns labelled Start Time and Finish Time. I am
trying to capture a date stamp in each column using this formula in
column f

=IF(E3="On Going",DateAndTime(),"")

and this formula in column g

=IF(E3="Complete",DateAndTime(),"")

when I select "On Going" from the drop down list the time is captured
in column f perfectly. However when I select "Complete" it captures
the time in column g but nulls the time previously captured in column f
(when "On Going" was selected) as I have changed the value from the
drop down list. Is there anyway that I can ensure the value captured
in Column f stays?

thanks in advance for any help.