Current Time Function

  • Thread starter Thread starter Shaun
  • Start date Start date
S

Shaun

I require a FORMULAE for example if i enter "M" in A1 cell then the B1 cell
should display the current time, like this the current time should appear in
B2 cell when i enter "M" in A2 cell.

Example
A1 B1
M 10:53:45

A2 B2
M 10:53:55

Any help on this?

Thanks.
 
=IF(A1="M",NOW(),"") and format the cell to display time
BUT - everytime the worksheet recalculates, the time will be updated.
You would need a subroutine to do this - are you prepared to use VBA?
best wishes
 
In B1 enter:

=IF(A1="M",NOW(),"") and format as time

then copy B1 downwards.
 
Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
If UCase(Target.Value) = "M" Then
With Target
.Offset(, 1).Value = Time
.NumberFormat = "hh:mm:ss"
End With
End If
End If
End Sub


Mike
 
Hi Gary,

I have already used the same formula, but when i paste the same formula for
the other cells then the formula returns the current time to all the cells.
So pls check and provide the solution for the same.

Thanks.
 
Hi Mike,

Thanks for your reply, But i need to get the result using formula, whether
it's possible?

Thanks

Shaun
 
Because formulas are recalculated every time a Workbook recalculates, and VBA
macros are run only when called. (In this case, just after you input a "M")

So, I'm afraid you can not accomplish what you want using just a formula.
 
If it is possible in VBA, then why it's not possible in formula?

Think about what your asking for. Your asking for a formula that only
calculates sometimes i.e. the first time you enter it. How would Excel handle
that? How would it remember which cells not to calculate? with great
difficulty I think.

Mike
 
Hi,

You can get the results with the formula that has been supplied by other
responses

=IF(A1="M",NOW(),"")

But then you will need VBA to turn this formula into a number, not a formula.

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
If Target = "M" Then
Target.Offset(0, 1) = Target.Offset(0, 1).Value
ElseIf Target <> "M" Then
Target.Offset(0, 1) = "=IF(RC[-1]=""M"",NOW(),"""")"
End If
End If
End Sub

this code will put the formula back into B1 if the user change the M in cell
A1 to anything else or clears that cell.
 
Back
Top