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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Shaun" wrote in message
...
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.

In B1 enter:

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

then copy B1 downwards.
--
Gary''s Student - gsnu200823


"Shaun" wrote:

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.

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" wrote:

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.

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.



"Gary''s Student" wrote:

In B1 enter:

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

then copy B1 downwards.
--
Gary''s Student - gsnu200823


"Shaun" wrote:

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.

Hi Bern,

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

Thanks...


"Bernard Liengme" wrote:

=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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Shaun" wrote in message
...
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.

Hi Mike,

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

Thanks

Shaun

"Mike H" wrote:

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" wrote:

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.

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Shaun" wrote:

Hi Bern,

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

Thanks...


"Bernard Liengme" wrote:

=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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Shaun" wrote in message
...
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.

You can't do it with a formula

"Shaun" wrote:

Hi Mike,

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

Thanks

Shaun

"Mike H" wrote:

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" wrote:

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



"Shaun" wrote:

Hi Bern,

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

Thanks...


"Bernard Liengme" wrote:

=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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Shaun" wrote in message
...
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.

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

--
Rick (MVP - Excel)


"Shaun" wrote in message
...
Hi Bern,

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

Thanks...


"Bernard Liengme" wrote:

=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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Shaun" wrote in message
...
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Shaun" wrote:

Hi Mike,

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

Thanks

Shaun

"Mike H" wrote:

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" wrote:

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.