Static time stamp

[lounestor]

=if(J39="incomplete",""if(j39="complete",Now(),J39)

this updates to the current date but i want it to save the time do static once it reaches complete.

I would much appreciate the help

thanks
Louise

[AndreRet]

Hello Louise, there is two ways of doing this. Try the one that suits your needs the best.

One way:

Put this macro in your MyExcelNameHere.xls or other startup workbook. Attach it
to a toolbar button, or give it a keyboard shortcut.

Public Sub ExtendedTime()
If TypeOf Selection Is Range Then
With ActiveCell
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
End If
End Sub

Or the second way -

You would have to create a macro, the easiest form of macro for this
would be an event macro. You could trigger it on replacing an empty
cell in a specific column, or when another column is filled in.

DateTimeStamp in Column A, on first entry in any other column on row (#datetimestamp)
http://www.mvps.org/dmcritchie/excel/event.htm#datetimestamp
Place current date constant in Column A when Column B changes (#autodate)
http://www.mvps.org/dmcritchie/excel/event.htm#autodate

The following will put the time into Column B when an entry is made in Column A
install by right click on sheet tab, View code, .place the following code there.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Time
Target.offset(0, 1).numberformat = "hh:mm:ss.00"
End If
End Sub