How to make the Result of a TODAY Function static?

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(C10>0;TODAY();IF(D10>0;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

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

Originally Posted by kscramm

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(C10>0;TODAY();IF(D10>0;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

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

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

Take a look here:

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

In article <[email protected]>,
kscramm <[email protected]> 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(C10>0;TODAY();IF(D10>0;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

Take a look here:

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

In article <[email protected]>,
kscramm <[email protected]> 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(C10>0;TODAY();IF(D10>0;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

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

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

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

Hi Kilian

Sources of information:

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

Regards

Steve