Date problem

  • Thread starter Thread starter Steve L
  • Start date Start date
S

Steve L

Your post has an April 6 date on it and today is only April 2. Did you check
your PC clock 1st? That may fix your problem.

Steve
 
Hi Drew

if you're using either the TODAY() or NOW() functions they will recalculate
each time you open the workbook .. .there is no function that will not
recalculate (this being the nature of functions), therefore you have two
choices
1) enter the date of the changes manually - quickest way to enter the
current date is control & ; (semi-colon)
2) use a macro to input the date into a cell
here's an example that puts the current date in cell B1 when cell A1
changes:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1") = Date
End If
End Sub
 
JulieD said:
Hi Drew

if you're using either the TODAY() or NOW() functions they will
recalculate each time you open the workbook .. .there is no function that
will not recalculate (this being the nature of functions), therefore you
have two choices
1) enter the date of the changes manually - quickest way to enter the
current date is control & ; (semi-colon)
2) use a macro to input the date into a cell
here's an example that puts the current date in cell B1 when cell A1
changes:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1") = Date
End If
End Sub
---
this code needs to go against the workhseet you want to use it in, so to
use it, right mouse click on the sheet tab, choose view code, copy & paste
the code into the right hand side of the screen. use alt & f11 to switch
back to your workbook and test.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
Click to expand...
Thanks Julie
.... and I found ctrl shift & ; = time all by myself :-) Is there a list of
all these shortcuts anywhere? I had written macros for date and time in the
past (what a waste of time)!

Peter
 
No, i had been changing my computer date to test my formulas
Thanks for response
Drew
 
OK that works fine, but a lot of hassle making that macro work for every
cell i need. Is there a shorter way to get a range of cells to work
this way, say, if any cell in column A changes, then make the cell next
to it display date, or do I have to assign this code to every cell i
need it on. Also, I use RC format and am having trouble finding the
equivalent code. I entered:

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address = "R1C1" Then
Range("R[1]C[1]") = Date
End If
End Sub

The result is that the date does not appear in another cell like it does
with the A1 style. All it does is apply a date format.

I thought that:

A1 = R[1]C[1]

and

$A$1 = R1C1

There must be some descrepency here. Bare with me, my 3rd day on Excel
or VBA and more important than making this work is learning how it works.

Thanks
Drew
 
Hi Drew

try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.column = 1 Then
target.offset(0,1) = Date
End If
End Sub


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
Drew Reed said:
OK that works fine, but a lot of hassle making that macro work for every
cell i need. Is there a shorter way to get a range of cells to work this
way, say, if any cell in column A changes, then make the cell next to it
display date, or do I have to assign this code to every cell i need it on.
Also, I use RC format and am having trouble finding the equivalent code.
I entered:

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address = "R1C1" Then
Range("R[1]C[1]") = Date
End If
End Sub

The result is that the date does not appear in another cell like it does
with the A1 style. All it does is apply a date format.

I thought that:

A1 = R[1]C[1]

and

$A$1 = R1C1

There must be some descrepency here. Bare with me, my 3rd day on Excel or
VBA and more important than making this work is learning how it works.

Thanks
Drew


Hi Drew

if you're using either the TODAY() or NOW() functions they will
recalculate each time you open the workbook .. .there is no function that
will not recalculate (this being the nature of functions), therefore you
have two choices
1) enter the date of the changes manually - quickest way to enter the
current date is control & ; (semi-colon)
2) use a macro to input the date into a cell
here's an example that puts the current date in cell B1 when cell A1
changes:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1") = Date
End If
End Sub
---
this code needs to go against the workhseet you want to use it in, so to
use it, right mouse click on the sheet tab, choose view code, copy &
paste the code into the right hand side of the screen. use alt & f11 to
switch back to your workbook and test.
Click to expand...
Click to expand...
 
I am trying to add a date function to my spreadsheet. I want it to
display the date that a certain value was entered. So far, all attempts
have resulted in a date that reupdates everytime I reopen excel. Is
there a function that will insert current date and make it static?
Thanks
 
Back
Top