Current Time Function

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

 

[Bernard Liengme]

=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

 

[Gary''s Student]

In B1 enter:

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

then copy B1 downwards.

 

[Mike H]

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

 

[Shaun]

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.

 

[Shaun]

Hi Bern,

If it is possible in VBA, then why it's not possible in formula?

Thanks...

 

[Shaun]

Hi Mike,

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

Thanks

Shaun

 

[Luke M]

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.

 

[Mike H]

You can't do it with a formula

Hi Mike,

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

Thanks

Shaun

 

[Mike H]

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

 

[Rick Rothstein]

It is "sort of possible" using formulas.

Check out the "Using circular references and worksheet functions" section at
this link...

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

 

[Shane Devenshire]

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.