Date and time keep disappearing

[quetzalc0atl]

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.

 

[Niek Otten]

http://www.mcgimpsey.com/excel/timestamp.html


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.
|

 

[Gord Dibben]

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