Hey,

I have a problem with the today Function. It appears that it changes
each day.
But that isn't what I want! I'd like to have a funtion that puts the
current Date in a field when Data is being added in the Row and then
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(C100;TODAY();IF(D100;TODAY();" "))

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

Thanks for your help already,
Kilian


--
kscramm
------------------------------------------------------------------------
kscramm's Profile: http://www.excelforum.com/member.php...o&userid=32695
View this thread: http://www.excelforum.com/showthread...hreadid=525132

Kilian,
A macro would certainly be able to do this but I'm still learning about
them. I can't see how you could keep the static result in a formula; one
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/Value
them so they become a fixed date. Not a very elegant solution, but it
would do the job.

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

Clive

kscramm Wrote:
Hey,

I have a problem with the today Function. It appears that it changes
each day.
But that isn't what I want! I'd like to have a funtion that puts the
current Date in a field when Data is being added in the Row and then
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(C100;TODAY();IF(D100;TODAY();" "))

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

Thanks for your help already,
Kilian


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525132

hey Clivey_UK,

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

greetz,

Kilian


--
kscramm
------------------------------------------------------------------------
kscramm's Profile: http://www.excelforum.com/member.php...o&userid=32695
View this thread: http://www.excelforum.com/showthread...hreadid=525132

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.

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

Thanks allready,

Kilian


--
kscramm
------------------------------------------------------------------------
kscramm's Profile: http://www.excelforum.com/member.php...o&userid=32695
View this thread: http://www.excelforum.com/showthread...hreadid=525132

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

Take a look he

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

In article ,
kscramm wrote:

Hey,

I have a problem with the today Function. It appears that it changes
each day.
But that isn't what I want! I'd like to have a funtion that puts the
current Date in a field when Data is being added in the Row and then
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(C100;TODAY();IF(D100;TODAY();" "))

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

Thanks for your help already,
Kilian

Well, thank you for your information but I have no use for that
information. i Don't understand Macro programming yet and therefor I
need some ready to paste in Macro. I'm sure the link you provided is
nice and probably very helpfull but I have no clue how to adapt that
macro to my needs. so please help me with the marco provided by
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 the
Datestamp instead of column E?!? And how can I make it disappear again
when No information (exept funtions) are in the Row?!?

best regards, Kilian


--
kscramm
------------------------------------------------------------------------
kscramm's Profile: http://www.excelforum.com/member.php...o&userid=32695
View this thread: http://www.excelforum.com/showthread...hreadid=525132

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

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

greetz, Kilian


--
kscramm
------------------------------------------------------------------------
kscramm's Profile: http://www.excelforum.com/member.php...o&userid=32695
View this thread: http://www.excelforum.com/showthread...hreadid=525132

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 me
the necessery to understand and programm macros myself?!?


--
kscramm
------------------------------------------------------------------------
kscramm's Profile: http://www.excelforum.com/member.php...o&userid=32695
View this thread: http://www.excelforum.com/showthread...hreadid=525132