How to make the Result of a TODAY Function static?

[kscramm]

Hey,

I have a problem with the today Function. It appears that it change
each day.
But that isn't what I want! I'd like to have a funtion that puts th
current Date in a field when Data is being added in the Row and the
having this date static the next day.
Here is what I had so far but I have no clue to make the date static:

=IF(C10>0;TODAY();IF(D10>0;TODAY();" "))

in this case it checks for information in field C10 and D10 and i
there is information it will add a Date like 2006-03-22. But new da
the field will change to 2006-03-23 and that is not what i want. I wan
it to stay the same when data is put in and the date is bein
presented.

Thanks for your help already,
Kilia

 

[Clivey_UK]

Kilian,
A macro would certainly be able to do this but I'm still learning abou
them. I can't see how you could keep the static result in a formula; on
non-macro way I can think of doing it is to, at the end of each day
copy all the dates shown for that day, and then Paste Special/Valu
them so they become a fixed date. Not a very elegant solution, but i
would do the job.

Alternatively press Ctrl ; in any cell to put the current date as
fixed value.

Clive

 

[kscramm]

hey Clivey_UK,

thank you for your assistance but I definetly need to have that jo
done automaticly. well, hope someone can help me here.

greetz,

Kilia

 

[Scoops]

Hi Kilian

Format your Column E to the desired date format.

Enter one of the following macros in the worksheet containing your
data:

1. To change the Date whenever you change the Data:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 3 Then .Offset(0, 2) = Date
If .Column = 4 Then .Offset(0, 1) = Date
End With
End Sub

2. To change the Date only for the original Date it's entered (i.e.
change it as many times you like on the first day, but change it on
subsequent days and it will still show the first day):

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 3 Then
If .Offset(0, 2) < Date Then
Exit Sub
Else
.Offset(0, 2) = Date
End If
ElseIf .Column = 4 Then
If .Offset(0, 1) < Date Then
Exit Sub
Else
.Offset(0, 1) = Date
End If
End If
End With
End Sub

You should be good to go.

 

[kscramm]

Nice, I realy like that makro you wrote. I have no idea what it mean
but it does exactly what i wanted to to do. One last thing, how can
change the Column to B instead of E? Could you help me with that a
well?

Thanks allready,

Kilia

 

[JE McGimpsey]

Take a look here:

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

 

[kscramm]

Well, thank you for your information but I have no use for tha
information. i Don't understand Macro programming yet and therefor
need some ready to paste in Macro. I'm sure the link you provided i
nice and probably very helpfull but I have no clue how to adapt tha
macro to my needs. so please help me with the marco provided b
Scoops:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 3 Then
If .Offset(0, 2) < Date Then
Exit Sub
Else
.Offset(0, 2) = Date
End If
ElseIf .Column = 4 Then
If .Offset(0, 1) < Date Then
Exit Sub
Else
.Offset(0, 1) = Date
End If
End If
End With
End Sub

where there to I have to make changes that Column B will get th
Datestamp instead of column E?!? And how can I make it disappear agai
when No information (exept funtions) are in the Row?!?

best regards, Kilia

 

[Scoops]

Hi Kilian

Assuming you're still updating either column B or C (3 or 4) then
simply change the Offset value and, as you didn't tell me which one
you'd chosen to use, here are the modifications to both:

1.
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 3 Then .Offset(0, -1) = Date
If .Column = 4 Then .Offset(0, -2) = Date
End With
End Sub

2.
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 3 Then
If .Offset(0, -1) < Date Then
Exit Sub
Else
.Offset(0, -1) = Date
End If
ElseIf .Column = 4 Then
If .Offset(0, -2) < Date Then
Exit Sub
Else
.Offset(0, -2) = Date
End If
End If
End With
End Sub

 

[Scoops]

Hi Kilian

I've just seen you're request to have no date if there is no value in a
cell.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
If .Column = 3 Then
If .Value = "" Then
.Offset(0, -1) = ""
Else
.Offset(0, -1) = Date
End If
End If
If .Column = 4 Then
If .Value = "" Then
.Offset(0, -2) = ""
Else
.Offset(0, -2) = Date
End If
End If
End With
Application.EnableEvents = True
End Sub

 

[kscramm]

thank you for your support, I will try to get the Macro to work. til
than thank you so far,

greetz, Kilia

 

[kscramm]

very nice, it works exactly the way I wanted it to act in my Excel
Sheet.
Great thanks to all of you supporting me in this manner.

greetz, Kilian

p.s. Do you know any book and or tutorial webpage that would show m
the necessery to understand and programm macros myself?!

 

[Scoops]

Hi Kilian

Sources of information:

http://j-walk.com/ss/
http://www.cpearson.com/excel.htm

Regards

Steve