Date function
 

Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

--
BGC

Date function
 

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"BC" wrote:

Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

--
BGC

Date function
 

A bit of programming would work. Put this code in the sheet module
behind the workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1").Calculate
End If
End Sub

--JP

On Sep 24, 1:56*pm, BC wrote:
Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. *I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

--
BGC

Date function
 

To enter a static date/time you can do this manually.

While holding CTRL key hit semi-colon then spacebar then SHIFT + semi-colon.

To do this via VBA code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value < "" Then
Excel.Range("B" & n).Value = Format(Now, "mm-dd-yyyy hh:mm:ss")
End If
End If
enditall:
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste to that module.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

On Thu, 24 Sep 2009 10:56:01 -0700, BC wrote:

Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

Date function
 

Jacob,
I tried this, closed, then reopened. File became read-only. Once I opened,
the date changed again.
Here's the formula I'm using.

=if(m198=0,now())
--
BGC


"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"BC" wrote:

Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

--
BGC

Date function
 

To record the timestamp you cannot use a formula..You will have to use a VBA
solution as mentioned in my earlier post.

If this post helps click Yes
---------------
Jacob Skaria


"BC" wrote:

Jacob,
I tried this, closed, then reopened. File became read-only. Once I opened,
the date changed again.
Here's the formula I'm using.

=if(m198=0,now())
--
BGC


"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"BC" wrote:

Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

--
BGC

Date function
 

OK,
How about just the date?
--
BGC


"Jacob Skaria" wrote:

To record the timestamp you cannot use a formula..You will have to use a VBA
solution as mentioned in my earlier post.

If this post helps click Yes
---------------
Jacob Skaria


"BC" wrote:

Jacob,
I tried this, closed, then reopened. File became read-only. Once I opened,
the date changed again.
Here's the formula I'm using.

=if(m198=0,now())
--
BGC


"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"BC" wrote:

Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

--
BGC

Date function
 

JP,
It didn't work. Closed and reopen and the time updated!
--
BGC


"JP" wrote:

A bit of programming would work. Put this code in the sheet module
behind the workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1").Calculate
End If
End Sub

--JP

On Sep 24, 1:56 pm, BC wrote:
Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

--
BGC

Date function
 

Gord,
This didn't work either. I held the time, but once I closed and reopened,
the time updated!
--
BGC


"Gord Dibben" wrote:

To enter a static date/time you can do this manually.

While holding CTRL key hit semi-colon then spacebar then SHIFT + semi-colon.

To do this via VBA code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value < "" Then
Excel.Range("B" & n).Value = Format(Now, "mm-dd-yyyy hh:mm:ss")
End If
End If
enditall:
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste to that module.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

On Thu, 24 Sep 2009 10:56:01 -0700, BC wrote:

Hi all,
I want cell b1 to display the current date and time based on cell a1's
entry. I want the date to remain constant and not update unless a1 has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

Date function
 

There is no difference between just the date and the date/time... you cannot
"freeze" either one of them using just a formula... it cannot be done
without using VB coding.

--
Rick (MVP - Excel)


"BC" wrote in message
...
OK,
How about just the date?
--
BGC


"Jacob Skaria" wrote:

To record the timestamp you cannot use a formula..You will have to use a
VBA
solution as mentioned in my earlier post.

If this post helps click Yes
---------------
Jacob Skaria


"BC" wrote:

Jacob,
I tried this, closed, then reopened. File became read-only. Once I
opened,
the date changed again.
Here's the formula I'm using.

=if(m198=0,now())
--
BGC


"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the
sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code
to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"BC" wrote:

Hi all,
I want cell b1 to display the current date and time based on cell
a1's
entry. I want the date to remain constant and not update unless a1
has been
changed.

I tried this and the date and time changes to current:

=if(a1=0,now())

Help!

--
BGC

Date function
 

Ugh, you could have mentioned earlier that it needed to stay the same
even if the workbook closed. In that case, you'll probably need to
write more event handlers.

--JP


On Sep 24, 2:46*pm, BC wrote:
JP,
It didn't work. *Closed and reopen and the time updated!
--
BGC



"JP" wrote:
A bit of programming would work. Put this code in the sheet module
behind the workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
* Range("B1").Calculate
End If
End Sub

--JP

Date function
 

Do not use a formula in B1.

Let the code enter the date/time for you.

Read ALL my instructions on where to place the code.


Gord

On Thu, 24 Sep 2009 11:49:02 -0700, BC wrote:

Gord,
This didn't work either. I held the time, but once I closed and reopened,
the time updated!